SQLServerCentral.com / SQL Server 7,2000 / Performance Tuning / SQL SERVER WEIRD Performance problem. Please help. / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 16:21:38 GMT20RE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxYeah expanding scope can make things like that happen. You just keep extending and extending until at some point (which I guess is now) it just needs to be reworked because the complexity has expanded so much. Feel free to post back if you need some help flushing out your queries and such.Thu, 05 Apr 2012 07:10:27 GMTSean LangeRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxill work on a total re-write. a query that starts with available coaches that joins subqueries that show availability, vacation, and conflicting classes. one big query instead of query after query. will definitely perform better.regards,Dannyps: the reason it was not written like this in first place is bc it started as a single task and we added more and more to it, no explained here (like show close classes starting/ending with +/- X hours and mins, consider a coach to be 'free' it he/she is staffed in overlapping class where there are no clients to it, the option to show busy coaches and teh remove from conflicting classes, etc.)thank you all.munscioWed, 04 Apr 2012 20:42:50 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxIt may run somewhat acceptably fast on the other databases but the performance gain needed from the sql engine will make those other pages scream too. This seems to be a rather complicated solution because as I suspected it goes well beyond the sql side. It will take a major retooling of the page processing too.Wed, 04 Apr 2012 12:29:56 GMTSean LangeRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote]If I run this test against MS Access or MySql it takes aprox. 4000ms.[/quote][quote][b]bullo (4/4/2012)[/b][hr]my page is not optimal? IT is not. But this is independent.2 other db engines run it fine.[/quote]4 seconds is not 'fine' by any definition of the word. I used to write web pages, any web page that takes 4 seconds just to query the data is a web page that is going to drive users away.It's not a case that MySQL is fast and SQL Server is slow. It's a case that the query on MySQL is slow and the query on SQL Server is slower. With proper code and proper indexing we can probably get this way under a second.Wed, 04 Apr 2012 12:28:24 GMTGilaMonsterRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/4/2012)[/b][hr]it runs VERY fast , but runs SUPER slow on sql server. I cannot understand it. i made every latency test.Probably something to do with SQLEXpress, but I dont think so.my page is not optimal? IT is not. But this is independent.2 other db engines run it fine.[/quote]For starters because every table requires a clustered index scan on each call, your indexing doesn't work well with your data requests.I asked some questions. When you can find some time to answer them I can probably assist you further. You're hooked into the 'why is it working here and not there'. I can't answer that, most of us can't. We're experts on *1* engine. We can help you in that *1* engine. I personally don't care about MySQL. If you'd like help to get it working well in SQL Server, work with us.I, personally, am very close to leaving you on your own with it. We're all volunteers, I have no particular investment in helping you other than it keeps some of my skills sharp and I like doing it. If it continues to feel like it's pulling teeth to get a question answered to help YOU, I will leave this to others with more patience to assist you.Wed, 04 Apr 2012 12:24:26 GMTEvil Kraig FRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/4/2012)[/b][hr]this is all for a SINGLE CLASS DAY.the plus second is to find overlapping/close classes. i have to make a drawing to came up with that logic. the overlapping could be any class where any time from start to end overlaps this class start to end.Anyway. Im in the process of re writing this page with ONE HUGE query with sub queries that show availability ,vacations, overlapping classes.BUT it is still a MISTERY why on mysql or access, if I make 250 times (one per active coach) this:query1 -&gt;run query1. query2 -&gt;run query2. query3-&gt;run query3. it runs VERY fast , but runs SUPER slow on sql server. I cannot understand it. i made every latency test.Probably something to do with SQLEXpress, but I dont think so.my page is not optimal? IT is not. But this is independent.2 other db engines run it fine.Regards,Danny[/quote]First Access &lt;&gt; SQL Server (any edition).Second, what database engine are you running in MySQL?You are basically trying to compare apples &lt;&gt; oranges &lt;&gt; pears. They all work differently and have different strengths and weaknesses. Making 1000 separate calls to MS SQL Server instead of one well written set-based query is going to behave differently than the same calls to Access or MySQL.Wed, 04 Apr 2012 12:21:43 GMTLynn PettisRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxthis is all for a SINGLE CLASS DAY.the plus second is to find overlapping/close classes. i have to make a drawing to came up with that logic. the overlapping could be any class where any time from start to end overlaps this class start to end.Anyway. Im in the process of re writing this page with ONE HUGE query with sub queries that show availability ,vacations, overlapping classes.BUT it is still a MISTERY why on mysql or access, if I make 250 times (one per active coach) this:query1 -&gt;run query1. query2 -&gt;run query2. query3-&gt;run query3. it runs VERY fast , but runs SUPER slow on sql server. I cannot understand it. i made every latency test.Probably something to do with SQLEXpress, but I dont think so.my page is not optimal? IT is not. But this is independent.2 other db engines run it fine.Regards,DannyWed, 04 Apr 2012 12:02:27 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxQuestions:Is '" &this_claday_starttime& "' the start time of the class in question or the start time of the day for all classes? Same question for '" &this_claday_endtime& "'What does " &this_claday_datenumber& " represent?What variable amount of time is used for '" &this_claday_starttime_plus_sec& "' and '" &this_claday_endtime_minus_sec& "'?If you'll notice in the reformatted code below, I ask if certain conditions in the where clause are repeats. Can you confirm that they're just cut/paste errors and I didn't foul up the parenthetical removals?'" &this_time_offset_start& "' is just '" &this_claday_endtime& "' + 40 minutes and '" &this_time_offset_end& "' is just '" &this_claday_starttime& "' - 40 minutes, right?Code reformatted for anyone else who's going through this, I've already started parameter swapping and cleaned out a bunch of extraneous parantheses in the where clauses to help with readibility. My guess is this is going to be a two pass query because the information described is used differently. One confirms availability, another produces warnings for another screen, if I understood Bullo right.[code="sql"]DECLARE @Today DATETIME, -- Replacing '" &this_claday_date& "' @ClassStarttime DATETIME -- Replacing '" &this_claday_starttime& "'-- this simply strips the time off the datetime stamp-- Replace GetDate() with your queried date from the parameter call, making this simple for now.SET @Today = DATEADD( dd, DATEDIFF( dd, 0, getdate()), 0)SELECT CoachID FROM coaches WHERE active=1-- Availability CheckSELECT DISTINCT tblCoachAvail.* FROM tblCoachAvail WHERE ( tblCoachAvail.av_day = 0 OR tblCoachAvail.av_day = "&this_claday_datenumber&" AND tblCoachAvail.av_datefrom &lt;= @Today AND tblCoachAvail.av_dateto &gt;= @Today AND tblCoachAvail.av_timefrom &lt;= '" &this_claday_starttime& "' AND tblCoachAvail.av_timeto &gt;= '" &this_claday_endtime& "' AND tblCoachAvail.av_not = 0 AND tblCoachAvail.av_coach = " &rsdetails("coachid")& "-- Vacation CheckSELECT tblCoachAvail.* FROM tblCoachAvail WHERE tblCoachAvail.av_not = 1 AND (tblCoachAvail.av_day = 0 OR tblCoachAvail.av_day = " &this_claday_datenumber& ") AND tblCoachAvail.av_datefrom &lt;= @Today AND tblCoachAvail.av_dateto &gt;= @Today AND ( (av_timefrom &gt;= '" &this_claday_starttime& "' AND av_timeto &lt;= '" &this_claday_endtime& "') -- Think this is a repeat of the above one OR (av_timefrom &lt;= '" &this_claday_starttime& "' AND av_timeto &gt;= '" &this_claday_endtime& "') OR (av_timefrom &lt;= '" &this_claday_starttime& "' AND av_timeto BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (av_timeto&gt;= '" &this_claday_endtime& "' AND av_timefrom BETWEEN '" &this_claday_starttime& "' AND '" &this_claday_endtime_minus_sec& "') ) AND av_coach = " &rsDetails.Fields("coachid")"-- Period checks/conflictsSELECT tblClassDays.classday_kids, tblClassDays.classday_starttime, tblClassDays.classday_endtime, tblClasses.cla_Location FROM tblCoachClassDays LEFT JOIN tblClassDays ON tblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id LEFT JOIN tblClasses ON tblClassDays.classday_classID = tblClasses.cla_ID WHERE tblClassDays.classday_noclass = 0 AND tblClassDays.classday_day = @Today AND ( (classday_starttime &gt;= '" &this_claday_starttime& "' AND classday_endtime&lt;='" &this_claday_endtime& "') -- Repeat again? OR (classday_starttime&lt;='" &this_claday_starttime& "' AND classday_endtime&gt;='" &this_claday_endtime& "') OR (classday_starttime&lt;='" &this_claday_starttime& "' AND classday_endtime BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (classday_endtime&gt;='" &this_claday_endtime& "' AND classday_starttime BETWEEN '" &this_claday_starttime& "' AND '" &this_claday_endtime_minus_sec& "') ) AND coachclass_coachid = &rsDetails.Fields("coachid")-- Time deviation warningsSELECT cla_Location, classday_starttime, classday_endtime FROM tblCoachClassDays LEFT JOIN tblClassDays ON tblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id LEFT JOIN tblClasses ON tblClassDays.classday_classID = tblClasses.cla_ID WHERE tblClassDays.classday_noclass = 0 AND tblClassDays.classday_day = @Today AND (tblClassDays.classday_starttime BETWEEN '" &this_claday_endtime& "' AND '" &this_time_offset_start& "' OR tblClassDays.classday_endtime BETWEEN '" &this_time_offset_end& "' AND '" &this_claday_starttime& "') AND coachclass_coachid = &rsDetails.Fields("coachid") [/code]Wed, 04 Apr 2012 11:46:36 GMTEvil Kraig FRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxThanks Kevin.Ill investigate thatWed, 04 Apr 2012 10:17:59 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxI was afraid of this: that set of stuff is well beyond what I consider appropriate for a forum assist. I don't have enough free time to dig into it - perhaps others will. I will say that you CAN combine many of those checks into a single statement (is coach avialable, do they have a conflict, are they on vacation, etc). I will add that there are some amazing things you can do (known as gaps and islands problem solutions) to very efficiently check who is available when and do it for all coaches for a large period. Those solutions are NOT trivial however, but you can find stuff online about them (Itzik Ben-Gan is known for these, and I think he has a chapter on it in one of the SQL Server MVP Deep Dives books as well as one of his TSQL books).Best of luck with it. If someone doesn't step up to spend the time to help you I encourage you to look for professional help.Wed, 04 Apr 2012 08:42:34 GMTTheSQLGuruRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxHi again. Sorry if i wasted ur time. here it goes on details...&lt;% 'this_claday_date, this_claday_datenumber, this_claday_starttime, this_claday_endtime taken from the CLASS DAY im staffing 'rsdetails("coachid") is the Coach Id taken from the main query 'main query on this loop: SELECT * FROM coaches WHERE active=1 (and some other filters, nothing to do with this.... Do Until (rsDetails.EOF) 'check if coach is available sqlavail = "SELECT DISTINCT tblCoachAvail.* FROM tblCoachAvail WHERE (((tblCoachAvail.av_day)=0 Or (tblCoachAvail.av_day)=" &this_claday_datenumber& ") AND ((tblCoachAvail.av_datefrom)&lt;='" &this_claday_date& "') AND ((tblCoachAvail.av_dateto)&gt;='" &this_claday_date& "') AND ((tblCoachAvail.av_timefrom)&lt;='" &this_claday_starttime& "') AND ((tblCoachAvail.av_timeto)&gt;='" &this_claday_endtime& "') AND ((tblCoachAvail.av_not)=0) AND ((tblCoachAvail.av_coach)=" &rsdetails("coachid")& "))" rs.open sqlavail,dbsss if NOT rs.EOF Then 'coach HAS availability for the class day rs.close 'exta queries to determine availvility, vacation and conflicts. '1) check if coach is on vacations ssqlvac = "SELECT tblCoachAvail.* FROM tblCoachAvail WHERE tblCoachAvail.av_not=1 AND ((tblCoachAvail.av_day)=0 Or (tblCoachAvail.av_day)=" &this_claday_datenumber& ") AND ((tblCoachAvail.av_datefrom)&lt;='" &this_claday_date& "') AND ((tblCoachAvail.av_dateto)&gt;='" &this_claday_date& "') AND ( (av_timefrom&gt;='" &this_claday_starttime& "' AND av_timeto&lt;='" &this_claday_endtime& "') OR (av_timefrom&lt;='" &this_claday_starttime& "' AND av_timeto&gt;='" &this_claday_endtime& "') OR (av_timefrom&lt;='" &this_claday_starttime& "' AND av_timeto BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (av_timeto&gt;='" &this_claday_endtime& "' AND av_timefrom BETWEEN '" &this_claday_starttime& "' AND '" &this_claday_endtime_minus_sec& "') ) AND av_coach=" &rsDetails.Fields("coachid") rs.open ssqlvac,dbsss If rs.EOF Then 'coach IS NOT in vacations for the class period rs.close '2)check if coach is in any class for the period (conflicts) ssql_inclass = "SELECT tblClassDays.classday_kids, tblClassDays.classday_starttime, tblClassDays.classday_endtime, tblClasses.cla_Location FROM (tblCoachClassDays LEFT JOIN tblClassDays ON tblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id) LEFT JOIN tblClasses ON tblClassDays.classday_classID = tblClasses.cla_ID WHERE (((tblClassDays.classday_noclass)=0) AND ((tblClassDays.classday_day)='" &this_claday_date& "')) AND ( (classday_starttime&gt;='" &this_claday_starttime& "' AND classday_endtime&lt;='" &this_claday_endtime& "') OR (classday_starttime&lt;='" &this_claday_starttime& "' AND classday_endtime&gt;='" &this_claday_endtime& "') OR (classday_starttime&lt;='" &this_claday_starttime& "' AND classday_endtime BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (classday_endtime&gt;='" &this_claday_endtime& "' AND classday_starttime BETWEEN '" &this_claday_starttime& "' AND '" &this_claday_endtime_minus_sec& "') ) AND coachclass_coachid=" &rsDetails.Fields("coachid") rs.open ssql_inclass,dbsss If rs.EOF Then 'coach IS NOT in any class during the period rs.close 'SHOW AS AVAILABLE........... 'OUTPUT TO SCREEN IN TABLE, ETC.... 'Calculate warnings: Close classes that happen between THIS class day Start time and End time including a setting of TIME tdeviation (i.e.: +/- 40Mins 'includes deviation in time of warning ssql_warn = "SELECT cla_Location, classday_starttime, classday_endtime FROM (tblCoachClassDays LEFT JOIN tblClassDays ON tblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id) LEFT JOIN tblClasses ON tblClassDays.classday_classID = tblClasses.cla_ID WHERE tblClassDays.classday_noclass=0 AND tblClassDays.classday_day='" &this_claday_date& "' AND (tblClassDays.classday_starttime BETWEEN '" &this_claday_endtime& "' AND '" &this_time_offset_start& "' OR tblClassDays.classday_endtime BETWEEN '" &this_time_offset_end& "' AND '" &this_claday_starttime& "') AND coachclass_coachid=" &rsDetails.Fields("coachid") rs.open ssql_warn,dbsss If NOT rs.EOF Then 'coach IS in classes betwen the warning period do while not rs.EOF 'Output to screen the close classes...... rs.MoveNext loop response.write("&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;") end if rs.close Else 'coach IS in a class during the CLASS DAY period 'DEPENDING ON THE SEARCH MODE IT MAY SHOW THIS COACH that is in conflicting classes. I DONT INCLUDE IT HERE FOR SIMPLICITY AND IMNOT USING THAT MODE TO TEST, 'SO NEVERMIND./...... End if 'coach IS NOT in any class during the period Else 'Coach is in vac rs.close End if 'vacation check query Else 'coach not available for the Class day rs.close 'DEPENDING ON THE SEARCH MODE IT MAY SHOW THIS NON AVAIL COACH. I DONT INCLUDE IT HERE FOR SIMPLICITY AND IMNOT USING THAT MODE TO TEST, 'SO NEVERMIND./...... End If rsDetails.MoveNext Loop rsDetails.Close Set rsDetails = Nothing Set rs = Nothing dbSSS.close set dbSSS = nothing %&gt;Tables:CREATE TABLE [dbo].[tblCoachAvail]( [av_index] [int] IDENTITY(1,1) NOT NULL, [av_coach] [smallint] NULL, [av_day] [tinyint] NULL, [av_datefrom] [date] NULL, [av_dateto] [date] NULL, [av_timefrom] [time](0) NULL, [av_timeto] [time](0) NULL, [av_type] [nvarchar](50) NULL, [av_not] [bit] NULL, CONSTRAINT [PK__tblCoach__31AADEEF689E4EE9] PRIMARY KEY CLUSTERED ( [av_index] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[tblCoachClassDays]( [coachclass_id] [int] IDENTITY(1,1) NOT NULL, [coachclass_cladayID] [int] NULL, [coachclass_coachID] [smallint] NULL, [coachclass_coachlevel] [nvarchar](6) NULL, [coachclass_invoiced] [bit] NULL, CONSTRAINT [PK__tblCoach__4B539DC86C6EDFCD] PRIMARY KEY CLUSTERED ( [coachclass_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[tblClassDays]( [classday_id] [int] IDENTITY(1,1) NOT NULL, [classday_classID] [int] NULL, [classday_day] [date] NULL, [classday_starttime] [time](0) NULL, [classday_endtime] [time](0) NULL, [classday_noclass] [bit] NULL, [Classday_note] [nvarchar](90) NULL, [classday_kids] [smallint] NULL, [attend] [bit] NULL, [classday_coachid_equip] [int] NULL, [Classday_payonnoclass] [bit] NULL, CONSTRAINT [PK__tblClass__E460650E26667738] PRIMARY KEY CLUSTERED ( [classday_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GONotes:1) Tblclasses doesnt count here. i LEFT JOIN to it just to include some fields when outputting to screen.2) tblclasses is the class 'description. Tblclassday is each class (topically, each CLASs has one CLASS DAY PER week)3) Tblcoachavaliablility: if av_not is FALSE it means coach IS available for the specified date and time range (used when searching for availability) if av_not is TRUE it means coach IS NOT available for the specified date and time range (used when searching for vacation)Let me know if it clarifies.Again ..THANK YOU!Wed, 04 Apr 2012 08:31:19 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxAnd 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.Wed, 04 Apr 2012 08:00:27 GMTSean LangeRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/3/2012)[/b][hr]Because the nature of the page and its options I need to make individual queries.[/quote]Why?That kind of code is not going to perform well on SQL.Wed, 04 Apr 2012 07:26:20 GMTGilaMonsterRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxADO 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.Wed, 04 Apr 2012 07:23:48 GMTTheSQLGuruRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/3/2012)[/b][hr]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 [u][b]exactly [/b][/u]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 = xxxxxrs.open query1 ,dbquery2 = xxxxxrs.open query2,dbquery3 = xxxxxrs.open query3,dbquery4 = xxxxxrs.open query4,dbAll 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[/quote]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.Tue, 03 Apr 2012 15:24:17 GMTLynn PettisRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/3/2012)[/b][hr]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[/quote]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.Tue, 03 Apr 2012 15:13:29 GMTEvil Kraig FRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxThank 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 [u][b]exactly [/b][/u]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 = xxxxxrs.open query1 ,dbquery2 = xxxxxrs.open query2,dbquery3 = xxxxxrs.open query3,dbquery4 = xxxxxrs.open query4,dbAll 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. MunscioTue, 03 Apr 2012 09:32:20 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxThank you! will do tomorrow, im leaving the office. good niteMon, 02 Apr 2012 16:43:33 GMTbulloRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxplease 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.Mon, 02 Apr 2012 16:35:28 GMTLynn PettisRE: SQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspx[quote][b]bullo (4/2/2012)[/b][hr]Hi, thank you for reading my first post.I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, [u]I’m not using Access with Linked Tables to SQL SRV.[/u] 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:[/quote]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.[quote]1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop2) 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.[/quote]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.[quote]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?[/quote]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.[quote]I REALLY appreciate any help.Thank you!Munscio[/quote]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.Mon, 02 Apr 2012 16:33:08 GMTEvil Kraig FSQL SERVER WEIRD Performance problem. Please help.http://www.sqlservercentral.com/Forums/Topic1276991-65-1.aspxHi, thank you for reading my first post.I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, [u]I’m not using Access with Linked Tables to SQL SRV.[/u] 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 loop2) 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 hereDoes 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!MunscioMon, 02 Apr 2012 16:02:41 GMTbullo