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 Wednesday, April 4, 2012 8:31 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
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!
Post #1278112
Posted Wednesday, April 4, 2012 8:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:33 PM
Points: 4,358, Visits: 6,193
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 at GMail
Post #1278122
Posted Wednesday, April 4, 2012 10:17 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
Thanks Kevin.

Ill investigate that
Post #1278223
Posted Wednesday, April 4, 2012 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
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& "'
@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 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_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")"

-- Period checks/conflicts
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 = @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_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")

-- Time deviation warnings
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 = @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")





- 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 #1278278
Posted Wednesday, April 4, 2012 12: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
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
Post #1278288
Posted Wednesday, April 4, 2012 12:21 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:08 PM
Points: 20,676, Visits: 32,266
bullo (4/4/2012)
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


First Access <> SQL Server (any edition).
Second, what database engine are you running in MySQL?

You are basically trying to compare apples <> oranges <> 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.



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 #1278301
Posted Wednesday, April 4, 2012 12:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
bullo (4/4/2012)

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.


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.



- 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 #1278305
Posted Wednesday, April 4, 2012 12:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
If I run this test against MS Access or MySql it takes aprox. 4000ms.


bullo (4/4/2012)

my page is not optimal? IT is not. But this is independent.2 other db engines run it fine.


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.



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 #1278311
Posted Wednesday, April 4, 2012 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
It 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.

_______________________________________________________________

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 #1278313
Posted Wednesday, April 4, 2012 8:42 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
ill 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,
Danny

ps: 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.
munscio
Post #1278529
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse