|
Hi, we upgraded SQL server 2000 to 2008 r2, we are running the following Select statement(which is in Stored Procedure) in 2008 it takes a long time but it will not results any thing.In Sql server 2000 it giving the result with in 20 mins.
(comments are locked)
|
|
Is it the same server? You have more issues then the upgrade to SQL 2008r2. The query isn't performing well in any edition! 20 min for a query like this? You don't mention anything about environment or data load, but I would guess You can decrease the time consumed by doing it right.first of all, you have a design flaw if you need to strip any spaces. How many ltrim trim can you find? And then you are grouping on the cases and trim functions. I guess you have a lot of scans and consumes a lot of CPU +1 definitely : bite the bullet, do a one-off clean up job now in the db and fix the interface so that it doesn't keep happening.
Jan 19 '11 at 02:27 AM
Kev Riley ♦♦
@Kev Abosuletly! I guess this is just one of many select statements with this kind of (nasty) logic, so the benefit of fixing the design is probably worth a lot of hard work.
Jan 19 '11 at 02:32 AM
Håkan Winther
@Håkan I'd be willing to bet that it takes less than 10 mins to update the lot!
Jan 19 '11 at 02:44 AM
Kev Riley ♦♦
@Kev It depends. :) They probably have this kind of design issue in the whole database, and a lot of code to review and test before they can put into production. But it is worth every second spent on doing it, and I agree with you, it will not take longer time to update the data then it takes to get it.
Jan 19 '11 at 03:56 AM
Håkan Winther
(comments are locked)
|
|
First of all, let me suggest that you try using the function Your next problem is that you are using functions in joins - this is not a recommended strategy as it can hinder the query optimiser from using indexes to satisfy the query. This can lead to table scans/clustered index scans (basically make SQL Server read the entire table that stores the data you need). This can cause a severe increase in I/O, Memory and CPU usage. The query also doesn't have schema qualification - this is something you should get into the habit of doing, as it does have a performance hit (MSDN link) You also have an inequality predicate in the where clause: For a little more help, I suggest you provide us with the table creation scripts (include any indexes) and also the execution plan of this query. The estimated execution plan can be retrieved by using the chord Ctrl-L when in SSMS - this will give you/us an idea of what is going on without having to wait for the query to complete. You will then get the graphical query plan displayed in SSMS which can be saved (right-click on the plan) and then uploaded here (it is an xml document, you just need to copy the contents and paste it in your answer here). I took the liberty of cleaning up the query to be a little easier to read - please check it to see it produces the same results and think about implementing it so you and your fellow devs have an easier time reading the code. mmmm, thats much more readable ! +1 - good advice too.
Jan 19 '11 at 12:22 AM
Fatherjack ♦♦
+1 for taking the time to really trying to format and read the complete select statement! :) I got tired of all the scrolling on the iPhone and gave up after a couple of seconds when I reallized that the code couldn't perform well, no matter of SQL edition. :D
Jan 19 '11 at 12:23 AM
Håkan Winther
@Fatherjack - it is better than before! I still stand by Hakan's statement of cleaning the data in the database instead.
Jan 19 '11 at 12:27 AM
WilliamD
(comments are locked)
|
|
Did you updated your statistics after upgrade? if not upgrade your statistics using UPDATE STATISTICS . Because Query optimizer may produce poor execution plans based on outdated statistics and results in poor performance. Also check your fragmentation level of your indexes. As an additional step check out the execution plan for any missing indexes. +1 I would accept your answer to the question, because the performance difference between the server is probably because of the statistics.
Jan 19 '11 at 12:50 AM
Håkan Winther
(comments are locked)
|
I like your question, because it suits well as a good example of how design issues are causing performance problems in the data retrieval process.