June 23, 2009 at 8:52 am
Hello,
I have a table with 100 Million records with 5 float columns
mpkey uniqueidentifier
totalshare float
mShar float
Fshare float
avgWshare float
AvgMShare float
mpKey totalshare FShare MShare AvgWShare AvgMShare
xxxxx1 .0000045 .00834333 .00343333 3.45 2.34
xxxxx2 .0000064 .00047777 .00033488 2.23 3.24
......
....
When I Select FShare and MShare columns in a query it takes longer time More than 4 min and I cancel the query( to fetch 15000 records)
When I have a query with out those 2 columns, it runs in 2 secs.
Is there any thing that delays the fetch when the values are 0.00033..(very small).
Thanks
THiru
June 23, 2009 at 8:58 am
Simply float takes 5-17 bytes for single value storage
when int takes 1 or 2 bytes to store a value. when you skip these columns in query
it is commonly skip that bytes needed for float search....
So, its fast
June 23, 2009 at 9:02 am
only two float columns that has very small values are causing the delay. other float columns when selected are not a problem.
June 23, 2009 at 9:05 am
Please post the query plans and full SQL statement. Im guessing that its not columns per-se that cause the issue but possibly a bookmark lookup to retrieve them
June 23, 2009 at 10:10 am
The Query and the actual plans with 2 columns included and with out 2 columns are attached
June 23, 2009 at 10:21 am
Hmmm , get rid of the @ tables and use # tables, i think your dataload is to large for them.
Also make sure your statistics are up to date , the estimates are way our on the actuals , although that could be due to the @ tables
June 23, 2009 at 1:15 pm
Thanks to all.
I rewrote the query and I got the results in less than 2 sec.
I am not sure if @ or # will delay the fetch but the joins are the cause in my query which after looking into the Plan I understood.
Thanks again to all.
Thiru
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply