April 4, 2012 at 12:21 pm
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.
April 4, 2012 at 12:24 pm
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.
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:28 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 12:29 pm
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 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:42 pm
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
April 5, 2012 at 7:10 am
Yeah 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.
_______________________________________________________________
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/
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply