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 |
logpop
Starting Member
16 Posts |
Posted - 2013-04-22 : 16:56:11
|
how do i convert this:col1 col2 col31 A C2 B D3 C A4 D Ato this:col1 col2 col31 A 32 B 43 C 14 D 1thanks!:) |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-22 : 17:04:07
|
There are bunch of way to do that. Here is a probably the most inefficient way:DECLARE @Foo TABLE (col1 int, col2 CHAR(1), col3 CHAR(1))INSERT @Foo VALUES(1, 'A', 'C'),(2, 'B', 'D'),(3, 'C', 'A'),(4, 'D', 'A')SELECT col1, col2, (SELECT TOP 1 col1 FROM @Foo AS B WHERE B.col2 = A.col3)FROM @Foo AS A |
|
|
logpop
Starting Member
16 Posts |
Posted - 2013-04-22 : 19:40:14
|
it works. how inefficient is it? table is not big, currently 300 entires, i doubt it will get much bigger, maybe max 10.000 entries theoretically. how much time would that take? can i measure its speed somehow in ms sql server management studio or somewhere? when i run it firs time it takes longer, but repeated runs it says it took 0.0 seconds. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-22 : 21:24:05
|
You can turn on the execution plan (control-M) and run it. There will be another pane next to the results window that shows the execution plan. You can hover over or right click and select properties on each of the steps to see all kinds of details about cpu time, IO cost etc. Or you can SET STATISTICS PROFILE ON and run it (or SET STATISTICS TIME ON or SET STATISTICS IO ON etc. each shows different information).That said, if you have only 300 entries, I wouldn't worry about performance. |
|
|
logpop
Starting Member
16 Posts |
Posted - 2013-04-23 : 23:30:31
|
I set SET STATISTICS TIME ONgoSET STATISTICS IO ON GOand i get total time it takes to execute whole query. its a big query, i use WITH keyword to kinda structure it into "functions"I found out deleting this line:(SELECT TOP 1 col1 FROM @Foo AS B WHERE B.col2 = A.col3)CPU time goes down from 1400 ms to 140 ms.and this:Table 'Worktable'. Scan count 105754, logical reads 322152changes to this:Table 'Worktable'. Scan count 2368, logical reads 33860One second is kinda unacceptable. ill have to find something else. if it cant be done with sql efficiently ill deal with it in ASP.NET program.Maybe i should mention that instead of @Foo im using a "view" created with WITH keyword. selecting all items from that viewtakes 156 ms.hmm or is 2 second query normal time for for example online store? lets say it takes 2 seconds to list all online store product categories, thats not good right? or do meaybe sql servers extensively use cache so 2 seconds is not that bad? i dont imagine it goez into db for every client viewing site but uses some caching system? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 01:24:59
|
what about a join?SELECT t.col1,t.col2,t1.col1FROM Table tINNER JOIN table t1ON t1.col2 = t.col3 convert it to LEFT JOIN if you wont have a value for col3 always------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
logpop
Starting Member
16 Posts |
Posted - 2013-04-24 : 07:50:14
|
Thanks dude it works much faster, 300 ms instead of 1400 ms. Now I just have to understand it. Here is my explanation tell me if I'm correct:We take these two tables:t:(1, 'A', 'C'),(2, 'B', 'D'),(3, 'C', 'A'),(4, 'D', 'A')t1:(1, 'A', 'C'),(2, 'B', 'D'),(3, 'C', 'A'),(4, 'D', 'A')and combine them into new temporary table using this algorithm:for each row tRow of t do this:t1Row = (find a row in t1 which has same col3 as tRow.col2)if found create new row in this way:newRow = (tRow.col1, tRow.col2, t1Row.col1)and add newRow to result dataset. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 08:34:50
|
yep...it uses the same table twice (self join) based on the relationship t1.col2 = t.col3------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|