Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL SERVER WEIRD Performance problem. Please help. Expand / Collapse
Author
Message
Posted Monday, April 2, 2012 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 2:52 PM
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
Post #1276991
Posted Monday, April 2, 2012 4:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1277007
Posted Monday, April 2, 2012 4:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.



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)
Post #1277008
Posted Monday, April 2, 2012 4:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 2:52 PM
Points: 7, Visits: 22
Thank you! will do tomorrow, im leaving the office. good nite
Post #1277013
Posted Tuesday, April 3, 2012 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 2:52 PM
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
Post #1277415
Posted Tuesday, April 3, 2012 3:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1277685
Posted Tuesday, April 3, 2012 3:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 20,727, Visits: 32,485
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.



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)
Post #1277691
Posted Wednesday, April 4, 2012 7:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:21 PM
Points: 4,394, Visits: 6,250
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
Post #1278027
Posted Wednesday, April 4, 2012 7:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 40,157, Visits: 36,543
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 2008, MVP
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

Post #1278034
Posted Wednesday, April 4, 2012 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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)
Post #1278083
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse