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 |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2011-07-15 : 05:03:45
|
Hi,Whilst changing a stored procedure recently, I spotted that the easiest way to get part of the job done was to change this join:on i.strId = sq.strId To this:on (@servicePrefix + i.strId) = sq.strId Now this wasn't the only change I made, but on re-running this stored procedure it's speed was drastically impaired - it's now taking almost double the time to run (and it wasn't fast in the first place). I'm fairly sure the tipping point came when I changed that join. So I'm wondering - will inserting a variable into a join in that manner cause a big efficiency hit? Cheers,Matt |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-15 : 07:45:12
|
| It would probably have been a lot better if you'd put this servicePrefix in a column of it's own instead of as a part of an ID. What's happening is that you're introducing a table/index scan on the i-table instead of a seek when you concatinate the id like this.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2011-07-15 : 07:50:29
|
quote:
Originally posted by Lumbago
It would probably have been a lot better if you'd put this servicePrefix in a column of it's own instead of as a part of an ID. What's happening is that you're introducing a table/index scan on the i-table instead of a seek when you concatinate the id like this.
Aha! Thank you - a little investigation and it looks like that's exactly what's going on.However it looks like I can circumvent the problem by doing the join the other way round - so removing the @servicePrefix from sq.strId rather than adding it to i.strId. sq is a lot, lot smaller and it doesn't have a proper index as it's a temp table. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-15 : 08:22:16
|
| A temp table can have a proper index if you create it but if the temp-table is really small (< 1000 rows) it might not be benificial (the overhead of adding the index would be greater than the overhead of not having an index). But if removing the servicePrefix makes the query perform well it's all good. I'd still advocate splitting the servicePrefix and the ID in to separate columns though :)- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
|
|
Variable in join slowing things down?