next »

[KaptainKrayola]

Here is a fun one for any of the MS SQL Server guys on the forum.

I had a query running like crap so i added an index on the column that seemed to need it and the query went from about 900ms to about 30ms on average which was the desired result.  However, about an hour later the query was running back in the 900ms range as though the index didn't exist.

Confused, I deleted and re-added the index and ZANG - 30ms query times again for about an hour then the query went right back up to the 900ms range.

Any clues?

[nutballs]

Leave it for a while and see if it eventually comes back down. Its might be building the index.

Otherwise, what type of data is it?
are you sure your query isnt doing something badly? indexes only speed up straight lookups, not aggregates (i think).

[KaptainKrayola]

I thought the same thing so i left it for a few days just in case it was something goofy going on with building the index.

the table only has 25k rows in it and its an int column.

its just a straight lookup with 2 joins.  nothing fancy which is why its so strange that it takes that long.

basically what's going on is that its a part table with a bunch of parts.  each part can have a "parent" part that it belongs with.  the query is looking up the child parts of the parent when the parent part is selected.   so its looking up WHERE partParentID= #whatever#

so i added the index on the partParentID column.

[nutballs]

oh 25k rows should have been only a few minutes at most unless the database is being pounded.

my guess is that it is not your partParentID then, but the columns you are joining on. indexed integer columns are about as fast as you can get. remember that an INNER JOIN is basically a WHERE, but you are saying WHERE tableA.column = tableB.column. So indexing is relevant there as well.

The only other thing I can think is that its not actually the database, but an outside process running at the same time, abusing the CPU or ram. If its part of a CF routine your running, thats your problem, its that your still using Cold Fusion...  jk
But that could be it. I have many times been blaming things on my database, but later to find out that its actually my non-db code screwing it up, or even some other process. It sounds like you are running in query analyzer though, so that wouldnt be it, unless you do have some routines running. watch your proc and ram while running your query and see whats going on.

one other thing is to run the query through the "estimated execution plan" (cntl-L from the query analyzer) which will show you what part of your query is going to use the most juice. that can help to point you in the right direction.

[KaptainKrayola]

yep yep - the keys im joining on are indexed in the proper places as well.  the weirdest part is that the index on the partParentID seems to help for a while then just stops.  I would be quick to blame coldfusion as well if the index never made a difference but the fact that it does for a time then stops leads me to lean towards SOMETHING on the DB end.

So i dunno.  I do agree with you on the problem being that I'm still using ColdFusion but unfortunately the client wants to keep paying me to keep supporting these CF apps so what can ya do, ya know? 

[perkiset]

Oh, oh, right. You use CF and MSSQL.

And you wonder what the problem is? 

::ducks for cover::

[KaptainKrayola]

Yeah, because I'm sure you've NEVER used technology outside of your preferred set of awesomeness for a buck eh Perks?

oh and while we are on the subject...




FUCK YOU!

[perkiset]

 

I'll have you know that I am a high priced ESCORT, not a whore.

And thanks for the FY Friday - it's been too long mang

[sassy bear]

Hey I know some guys about to RETIRE that modify COBOL code
on a room sized mainframe... Getting paid quite handsomely too..

FY Friday?  ahahaha fuck you all then!

[KaptainKrayola]

Well Nutballs you may have been on to something.

when going to the DB in CF i generally use a <cfqueryparam> to force the values to be there appropriate types in case anyone gets cute and tries breaking my queries.  Also, its supposed to, and here is the funny part, speed up CF talking to the DB because its guaranteed to be the correct datatype.

So, this particular query used a cfqueryparam for the partParentID in the WHERE clause.  So, i removed the cfqueryparam and sent it in normally and ZANG - query time down to 46ms.

Thanks for sucking more than we already knew you did, Adobe.  At least Adobe gets into the FyF spirit i guess 

*goes to delete every CFM file on his hard drive*

[nutballs]

do you have SQL server Manager? or are your doing everything blind via CF?

[KaptainKrayola]

both - i just couldn't see what that ONE query was doing differently than any other query and why it hated that one more than others - still have no idea really

once i fired up QA to test it there your CF theory became more viable.