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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2010-04-12 : 11:14:04
|
| I found that difference between 1) and 2). 1) is faster than 2). Why? How to make 2) faster?1) select * into #mytemp from order where OrderID='123456'2) decalre @orderid nvarchar(20)set @orderid = '123456'select * into #mytemp from order where OrderID=@orderid |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-12 : 12:12:06
|
In this example I think it is because NVARCHAR instead of VARCHAR.I think in the table the col OrderID isn't NVARCHAR.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-12 : 12:19:17
|
| Could it be a caching issue? eg, there's not much difference at all, but if you run the first one, the result of the disk read is already stored in memory when the second one is run, so it's faster? How many records does this insert affect? From a table called "Orders", with a single OrderID, I'd guess it only inserts a single record. I couldn't imagine that causing any noticeable difference.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2010-04-12 : 12:22:51
|
| Yes, the problem is NVARCHAR instead of VARCHAR |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-12 : 13:12:19
|
So we solved it? 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2010-04-12 : 14:05:25
|
| Yes but I do not know what is difference between them. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-13 : 01:28:20
|
The difference between them is that nvarchar is used to store unicode data and it needs two byte per character.So in your example above the OrderID in your table is VARCHAR and the @OrderID in the where clause is NVARCHAR and therefore SQL Server has to do an implizit CONVERT and this is slowing down the query.It is very bad if OrderID is an index because the CONVERT prevents the engine to use this index.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
Speed in select statement