I have a table with almost 2 mil records. There are two columns (Col1 int, Col2 int). Combination of them is unique. When i run a simple select statement like (select * from tblTableName Where Col1 = 5) with execution plan, cost is extremely high, even with PK(col1+col2). I tried to put clustered index on those columns but execution plan still shows "table scan" instead of "index seek". How can I speed this up ? I use that table mostly for search.
Dear friends We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Dear friends We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
I look after a database which is part of a third party CRM product. Theusers of the product complain of intermittant poor performance, thesuspicion is that some more senior users are running their own queries(the product allows users to do this). I've been asked by thedevelopment team to try to capture the details of long running queries.I've looked at the events listed in profiler and can't see one thatwould be useful. Ideally I want to know who is running which query thatis taking longer than x seconds.Any suggestionsTIALaurence
;with cte as ( select rank() over (partition by username order by guid ) as rank from MyTable where siteurl='myurl' and VisitedDateTime between '2007.02.05' and '2007.09.30' and IsFiltered=0 group by username,guid )
select count(rank) from cte where rank=2
this query is taking 6 seconds to execute .. 'MyTable' table is properly indexed.. 1 million rows are returned by common table expression.. i want to reduce execution time of this query to some milli seconds..please help me..
Iam using the below query to create a key using the column combinations and a seperator"%" to insert the same in to table my issue is with the perfomance of this query. This query is returing aroung 6000 records and its taking 11 seconds to return the result.is there any way that i can optimize this query to improve perfomance.Select (Item.ItemCode +'%'+ Product.Name + '%' + Quantity.ID) as Key1 from Items,Products,QualityPlease advice
In a database here we have a table that is a list of codes. Many other tables in our database have foreign keys to this table (T_TYPE_CODE). The joined column is an integer that is a clustered index on the table.
One of the developers here says that in one of his queries he has to OUTER JOIN to this table 15 times and that the OUTER JOIN is killing performance. He wants to add a record to T_TYPE_CODE that will represent NULL so that any NULL values in the tables that foreign key to this table will use that ID instead of NULL. In this way he could use all INNER JOINs.
To me this seems like a bad idea - NULL is NULL and creating a value to represent NULL will open a whole can of worms.
My question: Is there a performance hit for using OUTER JOINs against this table, considering that the join is on a single column and is a clustered, unique index?
Also, what problems can we expect to run into if we use a
Can someone tell me if it is possible to see the drives on the server using Perfomance Monitor? I so, where are tehy hiding because i struggled the wholed day!
We recently upgraded from sql 2000 to sql server 2005. Our system was developed using Microsoft visual Basic. Since we upgraded to sql server 2005 our system has been very slow. I suspect that it was because of the new sql 2005 installation that i made.Does anyone know how to solve this problem for me to be able to increase the perfomance speeed of our system again. It has been stressing me for a while now.
In my cube, I used Time Intelligence wizard to create calculated members such as YearToYearGrowth and YearToYear Growth%. But when I try to pull those two items in RS 2005 for more detailed levels, the performance of running MDX dataset is terrible even though my fact table has only 36234 rows of data and I have 6 dimensions (the largest dimension includes 37801 rows).
One thing that I did was removing ALLMEMBERS keywords in the MDX designer but I didn€™t improve much the time it takes to execute a query. Right now, it takes 1.5 minutes to execute may MDX query in RS 2005. Is there a way to improve the performance of MDX query (generated in RS) in RS 2005?
i have a table with 5 million rows (6 months data) .. every month approximately 1 million data is getting inserted..(not bulk insert) ..table is properly indexed.. There is a stored procedure to generate report based on this table.. (only this table no joins) .. stored procedure do lot of permutations (includes lot of temporary tables ,group by having,count() etc).. it takes 2 minutes to generate the report.. i want the report to be generated with out taking a second..
should i partition this table??? size of table is 2gb.. i dont know whether this table is a right candidate for partitioning or not.. please help me..
Dear ReaderI am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.I am able to identify the best candidate for the indexing.Below is the details I want to understand:AreaZIPCityCountyDistrictState/ProvinceCountryNow I want the data retrival optimization through Index. (you can suggest another idea, also)Entities Area,...., Country have independent tables.Example:Area_TableAreaID (PK)AreaThey have relationship- one to many- if you go from Country to Area.There is one more table:Location_Table (PK)LocationIDAreaIDZIPIDCityIDCountyIDDistrictIDState/ProvinceIDCountryID(Location_ID is further related to the Address of the contact.)GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.& simultaniously Location_Table is also being inserted with the details.Following is the situation of being queried these tables:(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)(2) Contacts have to be retrived on the basis of Area, ZIP, .....County. (Necessary Groupings are required )Example:If Contacts are queried Country Wise then the Display should beCountry1State1District1County1City1ZIP1Area1Area2ZIP2City2County2District2Country2Please Guide.SuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15*****************************************
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor: Call to table returning UDF, using local variables:
declare @orgauth varchar(50) set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth') declare @since datetime set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT * from deptAwardsFn ( dbo.MyParam('DeptAwards', 'OrgAuth') ,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) ) [20625 CPU, 1709010 Reads, 20632 Duration] (My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)--- (I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
I have a website with membership system. I've been through a lot implementing the site and I think I'm on the verge of full implementation. I can't get my registration page or my admin page to add, edit or delete users. I'm assuming that asp does not have proper permissions on SQL to do such. I'm running the site on a provider that has a SQL 2000 server. I access the database through VS2005 and SQL Server Management Studio Express. Is there a tutorial out there that could help a newbie to SQL understand how to check and correct my permissions issue (or find out that I'm haveing a different issue entirely. -Bill
Hi all, I`m a .NET desktop-app developer and i`m new to ASP.NET and web development, i start developping a web application with SQL Server database and i want to know if i can put all my tables in ASPNETDB? is any restriction with that? Regards
I am attempting to setup a membership instance for Site Server 3.0 using SQL 7 and try as I might, Site Server Membership will not see the server at all. I can do anything else I want to to that server and it works great (commerce, etc.). It all works OK under SQL 6.5 SP3 or later, just not SQL 7.
I am having problems deploying my web site from my machine to the hosting server.
Does any know of any special configurations that have to be perfomed before copying your web site to a server? All I have is a login in page that loads another page. The login page is launching okay but it does not load the second page.
I am upgrading my site from a VPS with a shared SQL server to a dedicated server and my own sql server 2003 standard. My site is an ecommerce site with about 10,000 users a month with between 10-20 people on the site at one time.
It is better to host both my site and the SQL server on the same system or is it better to have them on separate systems? if they are on the same system is 1 gig of ram enough? is will there really be much better performance with 2 gigs?
Any information on the best way to do this would be greatly appreciated!!!
I am trying access a FTP site present from a different server (remote work station). I am getting "The page cannot be displayed" error. I am able to access from my local machine.
I have setup a Flat File connection manager and set the location of the file as such:
ftp://10.1.1.1/FileName.txt
When I enter this URL into Start | Run on my machine it opens the txt file fine. Working in BIDS, when I create the flat file connection manager, it doesn't give me a message saying this file does not exist, indicating it is correct. When I create a flat file Source that uses the connection manager, the preview comes up blank. When I try to execute the step it gives an error saying it "cannot open datafile ftp://10.1.1.1/FileName.txt"
I need to place some site settings in my Report Manger.But in my Report Manager there is no Site Settings Icon.Is this installation problem please help me.
ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data?
Hi! My problem is...i gut this errormessage in Web Site Administration Tool: There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)
I recieved it after i try'ed to change the default Providers and use another DB then default. Now i want to go back to how it was from start, if i create a user and role - i want VS 2005 to create a SQL Express DB for me. Can anyone help me? I have even tryed to reinstalled VS 2005 but it didn't help. /Bamsen
hi alliam trying to build an asp.net webpart site, i donot want to use SQL Server Express for it, rathar than that i planned to use an sql server 2000 database , so far after doing this my pages is getting me this errorAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)where i surprise because i tell it in web.config to use my profile rathar than this default one, can any one tell me the secret behind thatRegards,
I am wondering how some of you update your database once it is already been in use. So take for example you have a few tables and users can enter data into the site. Now suppose you either add tables to the db or you add fields to an existing table. I am using sql express 2005 and just want to copy the db over with the copy website but it would overwrite any data users entered.Is there any easy solutions or suggestions for this.Thanks
