Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
redbrad0
Posting Yak Master
176 Posts |
 Posted - 2005-12-04 : 19:52:02
|
I am not sure the best way to get you guys to help me so hopefully this will be enough info. I am running about 1 million SP's that are the exact same a day with a different value. This is taking 187 CPU, 402 Reads, 0 Writes & 266 Duration. I took out the SP and hard coded in the values (only thing pass thru is domain-name.com thru the SP). Here is the image of what is returned. What should I look at to improve this Query? Quality NT Web Hosting & Design |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 21:41:57
|
| The best way to speed this up would be getting rid of the like '%value%'. If you can capture the domain name seperately and just query directly against it, you would get a lot better results. You might also try forcing a loop join (not sure I would do this). :) You can change the inner join to INNER LOOP JOIN if you want to try it though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
redbrad0
Posting Yak Master
176 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 23:13:40
|
The best way to speed this up would be getting rid of the like '%value%'. If you can capture the domain name seperately and just query directly against it, you would get a lot better results. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-05 : 15:09:08
|
| You also want to make sure your datatypes all match up. If User_id is varchar(150) and the Usersjobs_userid is varchar(100) then you will have some more time spent in the hashing of the two tables because sql server will first cast the datatypes to make them the same. I can't tell if your datatypes match or not... I'm just giving a general statement. Be sure to always mouse over the "hash" and see what join sql server actually used to join the tables. |
|
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2005-12-07 : 04:36:44
|
| Well I changed the query from using % to just a direct search and it went fromcpu 140, reads 402, writes 0, duration 140tocpu 0, reads 6, writes 0, duration 0So yes that was a big help... Thanks a tonAnother question is when inserting or searching via dates. Lets say I have the following queryUPDATE Users SET LastLoggedIn = '12/7/2005 3:29:36 AM' WHERE Users_ID = 1If I type this query into Enterprise Manager then it changes it to this..UPDATE Users SET LastLoggedIn = CONVERT(DATETIME, '2005-12-07 03:29:36', 102) WHERE Users_ID = 1Is that the correct way to insert the date? I just want to make sure I am inserting it so SQL Server does not have to convert it to the format that want.Quality NT Web Hosting & Design |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-07 : 09:21:30
|
| "Is that the correct way to insert the date"If you need to pass a date/time in "string format" then either use 'yyyymmdd' or 'yyyymmdd hh:mm:ss' or 'yyyy-mm-ddThh:mm:ss'.SQL Server will NOT treat these ambiguouslyAlternatively pass it using a variable of the appropriate date/time datatype in your application language - and then the translation should be done automatically for you.Kristen |
|
|
|
|
|
|
|
|
Improving Speed of Query