Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dboiler1
Starting Member
39 Posts |
 Posted - 2007-02-15 : 12:18:46
|
| One of our programmers has a stored procedure that joins two views into a new table.One of the views that it is grabbing is fairly complicated pulling from several tables, and can take quite awhile to run on its own. This stored procedure has been running on several databases for several months just fine. After switching to sql server 2005 it started taking almost 6 hours in one of the databases.As of now, it runs fine in one of the dbs in a few minutes and produces 50,000 records. The db that it takes almost 6 hours to complete produces only 17,000 records. The programmer has restricted the fields and gets it to run in under a minute - but it doesn't seem to matter which fields, just how many.After 3 or 4 of the records from the complex view, it goes from running in 1 min, to almost 6 hours again - no in between. It is the same code as the database where it runs fine and produces more records.Any thoughts would be greatly appreciated.Dave |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-15 : 12:21:42
|
| Wild guess:Are you JOINing (e..g in the VIEWs) columns/expressions which require implicit datatype conversions?Kristen |
 |
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-15 : 12:34:21
|
| I just asked the programmer and, no, nothing that might require implicit datatype conversions.dave |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-16 : 08:32:03
|
Sorry, no other ideas  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-16 : 08:35:27
|
| Corrupted indexes?Computed columns included in the view?Some horrible ORDER BYs in the views?Peter LarssonHelsingborg, Sweden |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-16 : 11:39:26
|
| parameter sniffing???....search here for examples of the problem and solutions to same.can you post the execution plan? |
|
|
|
evjo
Starting Member
20 Posts |
Posted - 2007-02-16 : 17:05:36
|
| Have you had a look at the Query plan?Adding or removing fields from the select can be the difference between a scan and seek.Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny. - Jack Handey |
|
|
|
dboiler1
Starting Member
39 Posts |
Posted - 2007-02-18 : 10:39:49
|
| Thank you all for your replies. I'll ask the programmer to respond and give you more information after I hear back.Dave |
|
|
|
|
|
|
|
|
6 hour Stored Procedure