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 |
|
Kristen
Test
22859 Posts |
 Posted - 2005-08-08 : 14:55:05
|
| I was looking over someone's shoulder today as they were having a problem, and I really can't figure it.They had some ASP code that was doing a "SELECT * FROM " + SomeTable + SomeOrderBywhere SomeTable and SomeOrderBy are ASP VBScript variables (and in this case SomeOrderBy was blank)Basically, this was a tool to dump a whole table (all columns, all rows) into Excel.Their client provides the database - and its predictably rubbish.So they have two tables they were trying, one with 200 rows, one with 2000 rows.Exporting the 200 row table took 6 seconds, the 2000 row timed out.So they changed it to SELECT TOP 500 * and it took over a minute.6 seconds for 200 rows, 60 seconds for 500 rows?The tables have about 30 columns, all int/tiny stuff - about 5 colums were CHAR with length less than 10. NO text or long varchar.There was NO PK, no indexes, not much of anything really!The user was the only one using the box (twin hyperthreaded IIS + SQL on same box with 2GB of RAM - of which about 1.5GB was used)I appreciate that this isn't very scientific! but I would have thought that a SELECT * with no WHERE clause / PK / etc. would be pretty much linearThe time wasting seemed to be the ASP loop that pulled each row and processed it - but it didn't do anything fancy - just used a few <TR><TD> ... </TD><TD> ... </TD></TR> thingies to format it. SO I reckon the main part of the delay was getting each row from SQL and processing it. Looking at SQL Profiler the "SELECT *" bit seemed to process instantly (there was another query straight after it, but before the first recordset starting being processed by ASP, which showed up immediately after the first)Any thoughts why this type of Query will take so long and use 100% CPU with one user attached?ThanksKristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-08 : 15:02:39
|
| Why aren't they using DTS or bcp to dump into Excel? Why bother writing a tool when a tool already exists?Anyway, I would bet that SELECT * FROM YourTable ORDER BY SomeColumn works very fast in Query Analyzer, right? If so, the problem is either with sending the data to the client (network bandwidth) or the application. What did the Duration column show in the SQL Profiler trace?Tara |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-08 : 15:17:15
|
you could use the getdata to get the whole recordset at one time... and then loop through the array...but as the goddess says... why bother  Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-08 : 15:25:18
|
| The "why bother" answer is that you can use an XLS mime/content type for a web page and your browser offers it up with a "Save to XLS" dialog box - very convient means of delivery to a client."If so, the problem is either with sending the data to the client (network bandwidth) or the application"[/i]"Fully agree. But my thinking is that if a 200 row query takes 6 seconds then a 500 row query would take 15 seconds - but it actually takes a minute, and 2000 rows times out (timeout limit was set to 10 minutes :-()[No one else using the machine]Or is my thought of "linear" invalid for this scenario?"getdata[/i]"I did suggest that [as it happens!] because I was wondering if some sort of awful cursor arrangement has unwitingly come-to-pass!Anyone want to stick their neck out on "Why is the performance non-linear"?ThanksKristen |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-08 : 15:33:12
|
quote:
Anyone want to stick their neck out on "Why is the performance non-linear"?
Firstly, try the experiment using a command line connection to SQL Server piping the output to a file to prove to yourself that its not SQL Server  One thing I've seen is where the consumer of the data is allocating memory for results in some funky way (doubling the amount each time with memzero-ing too). Eventually this will get sloooow.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-08-08 : 17:12:36
|
| do you have a good network connection? are the NICs configured to autodetect, or have you forced a FULL/100mbit connection? EDIT: I am assuming you have a fast ethernet network, if gigabit then autodetect is appropriate.If you have a network admin type guy there, have him put a sniffer on the line and see what is going on.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-09 : 01:05:10
|
| "doubling the amount of memory each time"I reckon that will be it, I'll mention it to the developer thanks Paul.We had a look with SQL Profiler to see what was hiting the server [the SQL is generated dynamically in the ASP application] and it was somthing like thisSELECT * FROM MyTableSELECT MyAliasName FROM MyColumnAliasLookup WHERE TheColumn = 'FOO'SELECT MyAliasName FROM MyColumnAliasLookup WHERE TheColumn = 'BAR'...Then, here, "The Big Wait"Basically it was grabbing a recordset for all the rows;then getting "Alias names" for each column for the presentation-layer stuff to display some "pretty" column headingsAnd then actually pulling the rows from the first recorset and just putting<TR><TD> ... </TD><TD> ... </TD></TR>around them.Might be that buffering is on so ASP is doing something like:MyBuffer = MyBuffer + SmallExtraBitand MyBuffer is getting ever larger to move around in memory.I'll let you know what we findKristen |
|
|
|
|
|
|
|
|
Performance non-linear