Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL SERVER WEIRD Performance problem. Please help.


SQL SERVER WEIRD Performance problem. Please help.

Author
Message
bullo
bullo
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
Hi, thank you for reading my first post.

I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, I’m not using Access with Linked Tables to SQL SRV. I have a page that is almost 10 times slower than the Access version of the app!!! It is a soccer school. The page is to Staff Coaches to Classes. It has VERY SIMPLE queries, but each one is repeated many times (one per coach). For example:

1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop
2) For each coach on the loop, I query the DB to check it he/she is available for the class (date and time)
3) If available, I have another query that check if she/he has any VACATION request (thus voiding availability).
4) If coach has no vacation, I query the DB again to see if coach if already staffed in a conflicting class (date/ time overlaps the class im searching)
5) If not in a conflicting class, I list the coach as available.
6) Cycle repeater per coach.

All tables have proper indexes (I rebuilt them). When running any query listed above isolated to test latency, SQL Server runs it faster than access. Im using SQL Express 2008 for development. I even tried with an INSTANCE that is on another computer. Same problem. I even tried migrating tables used in this page to MySql just to see performance, and it works wonderfully. I don’t think that a stored procedure helps here. The logic is on the page as explained. Queries are simple and work ok, so no bottle neck here

Does any want know what is going on? I’m searching for weeks.

In Performance monitor I see there are many batch req /sec and compilations per sec.

looks like Sql Server doesn't like to compile many many queries in a short time, even being simple queries?

I REALLY appreciate any help.
Thank you!
Munscio
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5703 Visits: 7660
bullo (4/2/2012)
Hi, thank you for reading my first post.

I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, I’m not using Access with Linked Tables to SQL SRV. I have a page that is almost 10 times slower than the Access version of the app!!! It is a soccer school. The page is to Staff Coaches to Classes. It has VERY SIMPLE queries, but each one is repeated many times (one per coach). For example:

DAO (the backend language for Access) is built to loop more effectively. SQL Server wants to work as effectively as it can in rowsets, single statement builds that it interprets into the execution plan.


1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop
2) For each coach on the loop, I query the DB to check it he/she is available for the class (date and time)
3) If available, I have another query that check if she/he has any VACATION request (thus voiding availability).
4) If coach has no vacation, I query the DB again to see if coach if already staffed in a conflicting class (date/ time overlaps the class im searching)
5) If not in a conflicting class, I list the coach as available.
6) Cycle repeater per coach.

This sounds amazingly painful, especially when in plain language, what I read in a single query:

Get a list of available coaches at a particular day/time where coachActive = 1 and VacationOverlap = 0 (that's a subquery of some kind, would need to see schema) AND conflictingClass = 0 (most likely a left join to scheduled classes table). Single query. That's WAY to may 'round trips' to the data, in general, that you're doing.


Does any want know what is going on? I’m searching for weeks.
In Performance monitor I see there are many batch req /sec and compilations per sec.

looks like Sql Server doesn't like to compile many many queries in a short time, even being simple queries?

Not over and over, and particularly not dynamic queries without parameters. Each one would need to be independently recompiled instead of using a cached compile and running against that.

I REALLY appreciate any help.
Thank you!
Munscio

If you want more effective help, we're going to need to see the schema, the queries, and the execution plan/.sqlplan. If you take a look at the links in my signature, the two on the left are what you want to walk you through getting us all the pieces of the puzzle for us to help you, the second one down on the left in particular.

This is an optimization and methodology issue. We're going to have to strip down your code and re-assemble it for you. The more pieces you give us to work with, the less mistakes and assumptions we'll make trying to assist you.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
please read and follow the instructions in the first article I reference below in my signature block regarding asking for help. It will walk you through what you need to post and how to do it to get the best answers possible.

From the descriptiong, it looks like you are using cursors and/or loops where a set-based alternative may be better.
We can't tell until we see your code.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
bullo
bullo
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
Thank you! will do tomorrow, im leaving the office. good nite
bullo
bullo
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
Thank you all. I knew it was goona be a difficult post....I think that in order to pinpoint a problem there are things that should be taken for granted. I totally agree (who doesn't?) that the less times you query the db, the better, so i could write all in one query as suggested (select * ...where coach is active and vacationoverlap=0, and available=1..)in subqueries.... but trust me. Because the nature of the page and its options I need to make individual queries. Anyway...Bottom line is, that would be more of a design and data base theory. But if we forget for a minute about it, this is exactly what is happening here:

Forget Logic. I made a new test. I wrote to screen ALL generated queries and run a new page.


set db= server.CreateObject("ADODB.Connection")
db.open Application("conn_str")
Set rs = Server.CreateObject("ADODB.Recordset")


query1 = xxxxx
rs.open query1 ,db

query2 = xxxxx
rs.open query2,db

query3 = xxxxx
rs.open query3,db

query4 = xxxxx
rs.open query4,db


All Queries 1,2,3, and 4 run 250 times for 250 different coaches.

If I run this test against MS Access or MySql it takes aprox. 4000ms.
If I run this test against Sql Server takes aprox. 8800ms. !!!!!!

why is that?? again...if I measure latency for each query, it will be faster in Sql Sever, but all page is slower....

thanks again. Munscio
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5703 Visits: 7660
bullo (4/3/2012)

If I run this test against MS Access or MySql it takes aprox. 4000ms.
If I run this test against Sql Server takes aprox. 8800ms. !!!!!!

why is that?? again...if I measure latency for each query, it will be faster in Sql Sever, but all page is slower....

thanks again. Munscio


Without seeing the actual calls, and what your code is doing with it on top of it, we can't answer you. The devil is in the details. It's like me asking you why each of my tires are faster between two cars but the faster car travels slower when put on the road. With nothing else to work from, you'll just stare at me and start asking questions.

Basically what we're doing; asking questions. It's too vague still.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
bullo (4/3/2012)
Thank you all. I knew it was goona be a difficult post....I think that in order to pinpoint a problem there are things that should be taken for granted. I totally agree (who doesn't?) that the less times you query the db, the better, so i could write all in one query as suggested (select * ...where coach is active and vacationoverlap=0, and available=1..)in subqueries.... but trust me. Because the nature of the page and its options I need to make individual queries. Anyway...Bottom line is, that would be more of a design and data base theory. But if we forget for a minute about it, this is exactly what is happening here:

Forget Logic. I made a new test. I wrote to screen ALL generated queries and run a new page.


set db= server.CreateObject("ADODB.Connection")
db.open Application("conn_str")
Set rs = Server.CreateObject("ADODB.Recordset")


query1 = xxxxx
rs.open query1 ,db

query2 = xxxxx
rs.open query2,db

query3 = xxxxx
rs.open query3,db

query4 = xxxxx
rs.open query4,db


All Queries 1,2,3, and 4 run 250 times for 250 different coaches.

If I run this test against MS Access or MySql it takes aprox. 4000ms.
If I run this test against Sql Server takes aprox. 8800ms. !!!!!!

why is that?? again...if I measure latency for each query, it will be faster in Sql Sever, but all page is slower....

thanks again. Munscio


No code, no ddl, no sample data; can't help. You are trying to compare apple to oranges to pears.

We are not mind readers, nor are we able to see what you see. We are volunteers, and sorry, but if you want our help you really need to provide the information we need to help you.

Read the first article in my signature block and follow the instructions.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5996 Visits: 8314
ADO Classic looping code like you have is killing you on the round-trip time. I don't need to see your queries to tell you you need to refactor them to stop asking the same question 250 times. make a SET-BASED QUERY so you can get 250 hits worth of data in ONE QUERY. I am 99.4% certain this will be possible (I have done this exact type of work for several clients over the years), and will be MUCH faster in total. You are currently making 250 TIMES as many round trips to the database as you need to make! If you follow directions (that you have been given several times now) we can probably help you refactor the first query and then you can take what you learn and apply it to the other 3.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
bullo (4/3/2012)
Because the nature of the page and its options I need to make individual queries.


Why?

That kind of code is not going to perform well on SQL.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
And to be honest your 4000ms is entirely too slow anyway. Basically you are trying to get your process back to an unacceptably slow speed. This means at best your page load is somewhere around 4-6 seconds. This is what happens when you run 1,000 queries instead of 4. It seems that your queries and your page need some serious retooling. I will join the list of people willing and able to help if you help us help you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search