Slow MSSQL Server ?

Bruce

Thanks for your help.

I'm disappointed with discountASP Support, I don't know why they don't do more. They want me to go to the forum to get help. You wisemx and Joel Thoms are the only ones that respond, and I appreciate it. Support is the one that should be able to correctthis problem. I know I need to do work on the Stored Procedure and application but I think the long run time at discountASP is a problem on there end.

I'll have to do more indexing and maybe break it up into smaller steps. but these are just work arounds. The Root cause is what needs to be addressed ! I'd be willing to bet other DB have the same problem they just don't know about it. I'll also defrag once I figure out how and if I can do that to there server and as a last resort I'll delete the whole DB and upload from scratch. Other than that I don't know what else to try.

Thanks again
albanello

 

I really don't think this is a problem on the DiscountASP end. Out of curiosity, what's the difference in the amount of data in your tables when you run on your machine versus DiscountASP? Could that account for the extra time?


Also,If you have a database process that takes longer than even a minute - especially when you're talking about less than a few thousand rows -that's a huge red flagthat your procedure needs to be reworked. Database engines aren't really built forthe kindof procedural programming that your massive stored procedure is doing - they're best at relational queries to store / load data that has already been processed by a procedural language like VB or C#.


The best thing you could do is find some other way to go about what you're doing. You could try doing most of your procedural stuff in C# or VB. Maybe by loading a row or more at a time, doing your processing on the rows in memory, then storing. Or maybe find a more relational approach to what you're currently doing procedurally. For example, you said in one of your posts that "The object is to go through a table adding one to each and EVERY cell and writing that value back to that cell". I'm pretty sure you cando that with a single SQL statement like this:


UPDATE MyTable SET Cell1 = Cell1 + 1, Cell2 = Cell2 + 1, Cell3 = Cell3 + 1, ... , CellN = CellN + 1


If you don't use a where clause, that will update each and every row in MyTable, adding one to each and every cell within that table - and it should do it damn near instantaneously for small data sets (a few thousand rows or so). It'll definitely be MUCH faster than using loops from TSQL to do it.


I think the main problem you're bumping into is that you're trying to force a relational database to act in a procedural manner. You said you're new to DB programming, so you may want to do some reading up on how relational databases work. They seem very wierd when you're used to normal procedural programming languages and it takes a bit of practice and patience to get your head around it.

 

Bruce

Customers are responsible to perform regular maintainence on their database. This is pretty much an industry standard.
Database gets fragmented over time. Not when you export / import.

OK ? I don't see a defrag utility in the control panel. I found a Shrink tool but it says it should be performed when the DB reaches 80% I'm at 2% should I run it anyway ? What else can I do as for as maintenance ? Do I do the maintenance to my local machine and then it goes to to discountASPwhen I Export the DBor do I do it to discountASP directly ?

I would suggest you check to make sure the index is in place on the hosting server.
I looked at the Stored Procedure at discountASP and it is the same one that is on my computer. I also used the show execution plane, in Query Analyzer, and ran it on the Stored Procedure at discountASP and locally the plane was exactly the same.

I never suggested my conputer was faster than discountASP Server, but support suggested that as a reason the Stored Procedure runs faster on my conputer.

Thank again.

albanello

 

Jamie, mjp and Gobannos

Thank you all for your responces they are REALLY appreciated ! I'll try to answer all your Suggestions/Comments/Questions please don't take any of my answers the wrong way because they are NOT ment that way.

Jamie:
<I really don't think this is a problem on the DiscountASP end..............>
The data is the same. I Export the whole DB EVERY time I change a Stored Procedure. The reason I say the problem is in MY database or at discountASP is because when I run the Stored Procedure locally it takes 1.5 minutes and when I run it at discountASP it takes 7 minutes. Now I can agree that my lack of experiance might result in a inefficient SP BUT the time to run the same SP should be a little closer time wise. Not 7 times slower.

<Also,If you have a database process that takes longer than even a minute ..............>
The Stored Procedure use to take 3.5 Minutes locally but thanks to help from Bruce, Joel Thoms and wisemx I used indexing to reduce it to 1.5 minutes locally. I am still trying to improve it further.

<Database engines aren't really built forthe kindof procedural ............>
I disclosed my experiance is limited (In Web/SQL application developement) this is a learning experiance for me. I was going from a book that said a Store Procedure was more efficient than executing the same logic in application code. I plane on reworking my code.... but again no matter what is done it will still run 7 times slower at discountASP. Right now that is the ROOT cause of the timeout problem.

<The best thing you could do is find some other way ............>
In the interest of shortening the correspondence I over simplified what I am trying to do..... it's not just a matter of adding one to each row of every column, the result of that cell, results in a second table being updated. In my 20 Years of programming I learned a long time ago if you can't get something to work do it a different way.

<You said you're new to DB programming, so you may want to do some reading up on how relational database...........>
The database is very simple the main table is only 990 rows two rows are added each week from the main table I create 10 other tables. The ONLY thing done with these 10 table is to display them in a DataGrid when the user request. SQL is overkill what I'm doing could probably be done with a spead sheet. But as I said this is a learning experiance and I wanted to get experiance with SQL,ASP.NET and Web Application Development.

mjp:
<I can understand why you may be disappointed with support, but support cannot do more to assist you........>
I'm not asking them to do my work. They ran the Query and agree it took 7 Minutes. My point is it takes 1.5 Minutes on my local computer. WHY the big difference ? There are all kinds of thing they could do e.g Run there own SP that has a known execution time and verify its execution time. I had to complain to get them to do what they did. The first guy told me to index.....(which I did) BUT again the question is WHY is it taking 7 time longer to run at discountASP. Every body avoids answering this question ! WHY is it taking 7 time longer to run at discountASP.
I have already said (I have limited experiance AND I am reworking my stuff)

Gobannos:
<I'm not sure about the data stored in your DB: but have you 'normalized' everything inside the DB?>
See my last responce to Jamie it is a very simple database that does not require normalizing (one main table used to create 10 unrelated tables) BUT assuming I am all wet and Normalizing needs to be done the run time between discountASP and local should be closer ! (WHY is it taking 7 time longer to run at discountASP)

Thanks again I appreciate your help It is very difficult to communicate all the information about my database.
BUT for the last time, Given a Stored Procedure WHY is it taking 7 times longer to run at discountASP than locally.

Jamie, mjp and Gobannos
Please Help
Could one of YOU run one of your SP locally and at discountASP and see what your difference in execution time is. That would REALLY help. Because while I'm sure the problem is at discountASP I'm NOT sure if it's there server OR myDataBase e.g Fragmented.... or something else OTHER than a inefficient Stored Procedure.

One last thing are any of you a Database Adminstrator ? I would really appreciate some guidence incheck my database e.g deframenting..... and what kind of stuff I can do with it located at discountASP

albanello

 

Albanello,

I only have a few stored procedures (very simple ones) that I run and they run faster on the DiscountASP servers than they do on my development servers. So at least for me there's no slowdown on the DASP side. I'm going to take a closer look at your stored procedure though, because now I'm curious what it does. I'll let you know if I see anything in there that could speed you up.

Jamie

 

Albanello,


One more quick question. I noticed in the version of your SP that you posted, it was edited to run inside Query Analyzer. Is that how you're doing your timing tests? If so, that explains why your times are different. When run from Query Analyzer instead of as a stored procedure, each of the select, update, insert, or whatever commands gets shot across the network from the computer you're running query analyzer on to the DASP servers. In a loop like you've got, that can be alot of network traffic and could easily account for the extra time. You obviously wouldn't get as much extra time on a local dev box because the network transit time (if any) would be less. You could try running it from Query Analyzer using the "exec" command to run the whole thing at once to get a more accurate timing.


If you're running it as a stored procedure though and not from Query Analyzer, disregard this.


Jamie

 

Jamie

Thank You for your responce

<......they run faster on the DiscountASP servers........>
That's what I would expect, but that not what I saw in my case

<I'm going to take a closer look at your stored procedure though........>
I have modified it to index, as I said that lowered my execution time loccally from 3.5 to 1.5 minutes. The last While loop is where all the time was being used, thats where I did the indexing of the Read/Writes to the Since Hit and Skip Hit Tables. I determined this while loop was the problem by time stamping to a seperate table different point in the SP execution you'll see that in the post, dated 10/04/06, that has the original SP .

<your second post>
Initialilly I ranthe stored procedure modifiedfor query analyzerlocally in query analyzer and then I ran my Web Application at discountASP. Then to make the test more consistant I opened query analyzer and ran a EXEC connected to my local database and got 1.5 Minutes then I ran the same EXEC connected to my SQL at discountASP and got 7 Minutes. discountASP ran the same EXEC command in house and verified the 7 Minute time.

Would you like me to post the updated SP, that include indexing ?

The basic purpose is to create the Since Hit and Skip Hit tables using the Number table. I can go into more detail if you like but it gets kind of complicated to explain. You almost need to understand what I'm trying to do to understand the SP logic. again any improvements you may find will lower my time and discountASP's time by the same percent. discountASP will still be longer RIGHT ?

If your SP did not have a difference locally compared to discountASP then the problem has to be with my database. I've had the site for a year now .

Question:
When I Export the database to discountASP does the database write each table, stored procedure....... in contiguous memory or does it try to write at the same location and fragment to another location if the new table, SP..... is larger than the old. If the later I can see how the data could be fragmented.

albanello

 

Albanello:

I'm not sure about the data stored in your DB: but have you 'normalized' everything inside the DB?
A DB which is not 'normalized' can cause also in running unnecessary queries (SELECT DISTINCT for ex.) instead of selecting less data from another table which speeds up queries.

I case you haven't done the normalizing, here's some further reading:
- en.wikipedia.org/wiki/Database_normalization
- www.datamodel.org/NormalizationRules.html

However, i really do not understand what you trying to achieve with your sp, but i think i'm not alone... /emoticons/tongue.gif

HTH

 

Have you verified your DB is being exported properly?

I've seen exported databases lose their indexing and lose their primary keys as well.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Albanello,

I'm not sure how it gets written as far as whether that's causing fragmentation or not. I suppose you could try wiping out your database and recreating it, then adding the data back in to test that. The only solution I can suggest is to try to do more of your procedural programming in C# or VB. Try writing a quick and dirty C# or VB program that does nothing but grab all of the data out of your main table into a DataSet or custom list type and see how long that takes. If it's pretty quick, then try processing that data from within C# or VB and populating your GUI with that. My guess is that a single hit to grab all 1000 rows, then looping through each cell in those 1000 rows is going to be quicker than 7 minutes and maybe even quicker than 1.5 minutes since all of your data seems to be integers. Aside from that, I'm all out of ideas. Sorry.

Jamie

 

I can understand why you may be disappointed with support, but support cannot do more to assist you. Troubleshooting customer code or database queries is outside of the scope of their duties. Troubleshooting non-server-related issues fall onto the user. It has to be that way in order for our support department to function. The situation is the same at the vast majority of hosting companies.

mjp
DiscountASP.NET
<SUB><SUP>http://DiscountASP.NET

 

Jamie and Joel Thomas

Joel Thomas:
<Have you verified your DB is being exported properly.....>
I've looked at the two tables, in Query Analyzer object browser,that have index created by the Stored Procedure and the Indexes are there. Is there another way to dothe verifying? What about my fragmentation question ?


Question:
When I Export the database to discountASP does the database write each table, stored procedure....... in contiguous memory or does it try to write at the same location and fragment to another location if the new table, SP..... is larger than the old. If the later I can see how it would get fragmented.

Is there a way to tell what the fragmentation is and can I defrag if it is. I'm not sure what I am allowed to do to my Database (Maintenance wise). Can I do Anything/Everything Enterprise Manager allows ?

Jamie:
<I suppose you could try wiping out your database......>
OK so your saying go into Query Analyzer or Enterprise Manager and delete ALL Table and Stored Procedures and then Export the Main table and Stored Procedure again then run the stored procedure to recreate the table. Is that what you are suggesting ? Or are you really saying DELETE the whole database and reexport it to discountASP.
<.......and populating your GUI with that........>
The reason I create the tables ahead of time is so when the user requests a display of a table it is already created all I have to do is get it and put it in a datagrid (As a side note what I do is get all of them once and cashe in a dataset for use by all users).
A slight modification of what your suggesting is, I would grag the data out of the main table create the tables in application code then write the tables back to the SQL server. How does that sound ? The only problem with this approch is it is going to require a massive rework

Jamie and Joel Thomas:
I'll try both of your suggestion this week end. Thank for the input But I would still like to figure out what the real problem is (discountASP slower than Locally) ! I really need suggestion like Jamie's "delete and reexport" and Joel Thoms's "Verify did not lose index....." The slower running is the real problem !

Please don't take me wrong your suggestion to improve the processing time are appreciated and I WILL work on improving my method of creating these table. I really want to figure out why the difference in execution time because I'm afraid if I don't figure this out now it will come back and bite me later. It has to be something with my data base some how it ?????? I don't know I'm just throwing out stuff. It got fragmented contaminated........to the point it's causing the stored procedures to run slower.

I'll let you know the results of you suggestion.

Thanks again.
albanello

 

Hi


If anybody is still out there and interested I have started a new thread "Slow Running MS SQL Server (Round 2)" this one is getting to long.


Still appreciate your help Thanks.


albanello