April 2, 2012 at 4:02 pm
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
April 2, 2012 at 4:33 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 2, 2012 at 4:35 pm
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.
April 2, 2012 at 4:43 pm
Thank you! will do tomorrow, im leaving the office. good nite
April 3, 2012 at 9:32 am
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
April 3, 2012 at 3:13 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 3:24 pm
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.
April 4, 2012 at 7:23 am
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 on googles mail service
April 4, 2012 at 7:26 am
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
April 4, 2012 at 8:00 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2012 at 8:31 am
Hi again. Sorry if i wasted ur time. here it goes on details...
<%
'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)<='" &this_claday_date& "') AND ((tblCoachAvail.av_dateto)>='" &this_claday_date& "') AND ((tblCoachAvail.av_timefrom)<='" &this_claday_starttime& "') AND ((tblCoachAvail.av_timeto)>='" &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)<='" &this_claday_date& "') AND ((tblCoachAvail.av_dateto)>='" &this_claday_date& "') AND ( (av_timefrom>='" &this_claday_starttime& "' AND av_timeto<='" &this_claday_endtime& "') OR (av_timefrom<='" &this_claday_starttime& "' AND av_timeto>='" &this_claday_endtime& "') OR (av_timefrom<='" &this_claday_starttime& "' AND av_timeto BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (av_timeto>='" &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>='" &this_claday_starttime& "' AND classday_endtime<='" &this_claday_endtime& "') OR (classday_starttime<='" &this_claday_starttime& "' AND classday_endtime>='" &this_claday_endtime& "') OR (classday_starttime<='" &this_claday_starttime& "' AND classday_endtime BETWEEN '" &this_claday_starttime_plus_sec& "' AND '" &this_claday_endtime& "') OR (classday_endtime>='" &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("</font></td></tr>")
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
%>
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]
GO
CREATE 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]
GO
CREATE 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]
GO
Notes:
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!
April 4, 2012 at 8:42 am
I 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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 4, 2012 at 10:17 am
Thanks Kevin.
Ill investigate that
April 4, 2012 at 11:46 am
Questions:
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.
DECLARE @Today DATETIME, -- Replacing '" &this_claday_date& "'
@ClassStarttimeDATETIME -- 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 Check
SELECT DISTINCT
tblCoachAvail.*
FROM
tblCoachAvail
WHERE
( tblCoachAvail.av_day = 0
OR tblCoachAvail.av_day = "&this_claday_datenumber&"
AND tblCoachAvail.av_datefrom <= @Today
AND tblCoachAvail.av_dateto >= @Today
AND tblCoachAvail.av_timefrom <= '" &this_claday_starttime& "'
AND tblCoachAvail.av_timeto >= '" &this_claday_endtime& "'
AND tblCoachAvail.av_not = 0
AND tblCoachAvail.av_coach = " &rsdetails("coachid")& "
-- Vacation Check
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 <= @Today
AND tblCoachAvail.av_dateto >= @Today
AND ( (av_timefrom >= '" &this_claday_starttime& "'
AND av_timeto <= '" &this_claday_endtime& "')
-- Think this is a repeat of the above one
OR (av_timefrom <= '" &this_claday_starttime& "'
AND av_timeto >= '" &this_claday_endtime& "')
OR (av_timefrom <= '" &this_claday_starttime& "'
AND av_timetoBETWEEN '" &this_claday_starttime_plus_sec& "'
AND '" &this_claday_endtime& "')
OR (av_timeto>= '" &this_claday_endtime& "'
AND av_timefrom BETWEEN '" &this_claday_starttime& "'
AND '" &this_claday_endtime_minus_sec& "') )
AND av_coach = " &rsDetails.Fields("coachid")"
-- Period checks/conflicts
SELECT
tblClassDays.classday_kids,
tblClassDays.classday_starttime,
tblClassDays.classday_endtime,
tblClasses.cla_Location
FROM
tblCoachClassDays
LEFT JOIN
tblClassDays
ONtblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id
LEFT JOIN
tblClasses
ONtblClassDays.classday_classID = tblClasses.cla_ID
WHERE
tblClassDays.classday_noclass = 0
AND tblClassDays.classday_day = @Today
AND ( (classday_starttime >= '" &this_claday_starttime& "'
AND classday_endtime<='" &this_claday_endtime& "')
-- Repeat again?
OR (classday_starttime<='" &this_claday_starttime& "'
AND classday_endtime>='" &this_claday_endtime& "')
OR (classday_starttime<='" &this_claday_starttime& "'
AND classday_endtimeBETWEEN '" &this_claday_starttime_plus_sec& "'
AND '" &this_claday_endtime& "')
OR (classday_endtime>='" &this_claday_endtime& "'
AND classday_starttimeBETWEEN '" &this_claday_starttime& "'
AND '" &this_claday_endtime_minus_sec& "') )
AND coachclass_coachid = &rsDetails.Fields("coachid")
-- Time deviation warnings
SELECT
cla_Location,
classday_starttime,
classday_endtime
FROM
tblCoachClassDays
LEFT JOIN
tblClassDays
ONtblCoachClassDays.coachclass_cladayID = tblClassDays.classday_id
LEFT JOIN
tblClasses
ONtblClassDays.classday_classID = tblClasses.cla_ID
WHERE
tblClassDays.classday_noclass = 0
AND tblClassDays.classday_day = @Today
AND (tblClassDays.classday_starttimeBETWEEN '" &this_claday_endtime& "'
AND '" &this_time_offset_start& "'
OR tblClassDays.classday_endtimeBETWEEN '" &this_time_offset_end& "'
AND '" &this_claday_starttime& "')
AND coachclass_coachid = &rsDetails.Fields("coachid")
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2012 at 12:02 pm
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 ->run query1. query2 ->run query2. query3->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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply