July 8, 2010 at 2:09 am
Hi Guys,
We are currently using SQL Server 2005 standard edition.
There is an Entity/Table that contains 7 columns with 2 column as Clustered Index and 4 columns as Non-Clustered Index. It contains data nearly 9,000,000 records. We are facing performance issues if we query for a wider range of data depending on user request.
Is there any way of Implementing Table partitioning Concept that is available on SQL Server 2005 Enterprise Edition IN SQL Server 2005 STANDARD EDITION
Regards,
Ganesh
July 8, 2010 at 2:37 am
IMO , poor performance is generally the cause of poor TSQL and poor design.
Please post DDL , queries and Execution Plans
July 8, 2010 at 2:47 am
9 million rows shouldn't be that hard to deal with if there are appropriate indexes and well-written SQL. Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 4:41 am
Hi Guys,
I have attached the Table Schema and the SP used to execute.
July 8, 2010 at 4:50 am
Please see this article
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Additionally , i wouldnt use Table variable here , i would be using temp tables.
Also i suspect that with smarter SQL you should be able to remove all looping , though i dont have the time and inclination to do that for you.
July 8, 2010 at 5:11 am
Dave Ballantyne (7/8/2010)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/Additionally , i wouldnt use Table variable here , i would be using temp tables.
Also i suspect that with smarter SQL you should be able to remove all looping , though i dont have the time and inclination to do that for you.
Agree on all points. Convert to temp tables and index them appropriately
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 5:18 am
What do you guys think of index that has been done on the Table provided?
Should I created individual indexes for each column in the where clause?
July 8, 2010 at 5:20 am
I agree along with Gail... ➡
July 8, 2010 at 5:29 am
ranganathan.arumugham (7/8/2010)
What do you guys think of index that has been done on the Table provided?
Not as useful as it could be, because it's missing two columns used in the where clause and because of the 'catch-all' type query.
Should I created individual indexes for each column in the where clause?
No. SQL will likely only use one of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 5:36 am
GilaMonster (7/8/2010)
Not as useful as it could be, because it's missing two columns used in the where clause and because of the 'catch-all' type query
That is *NOT* to say that if you add them on the index will suddenly become useful. The catch-all query is doubtless causing a table/clustered index scan as SqlServer has to look at every row to resolve the query.
The link i gave earlier will give you the insight as to why this happens and what you can do about it.
July 8, 2010 at 5:37 am
GilaMonster (7/8/2010)
Not as useful as it could be, because it's missing two columns used in the where clause and because of the 'catch-all' type query.
That is *NOT* to say that if you add them on the index will suddenly become useful. The catch-all query is doubtless causing a table/clustered index scan as SqlServer has to look at every row to resolve the query.
The link i gave earlier will give you the insight as to why this happens and what you can do about it.
[/quote]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply