Need some help tweaking SQL select

Question

Post reply

Need some help tweaking SQL select

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Hey petey20, can you settle this on for us?

Can you rerun the query without the converts but with the missing link and tell us the time?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Deterministic or not, the argument (and therefore the result) of the CONVERT function varies every time the function is called. If 250,000 rows are returned, that's approx 40 million invocations of CONVERT.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Good point.. but I'll still wait for the poster's answer on this one .

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 4

A NON DETERMINISTIC function yes 40 million times but a DETERMINISTIC function NO

I saw in the first post "I have a view" so definitly was DETERMINISTIC functions so ...

I ll wait for the time exec if petey20 will post with convert and without

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Hey Vasc do you know of an article that compares both NON DETERMINISTIC and DETERMINISTIC for speed?

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 6

Couldn't find one ...

But usually DETERMINISTIC ones will cache the result and the result will be used when have the same param

I couldn't find an article to describe exactly how is managed by SQL Server engine

Kindest Regards,

Vasc

petey20 SSCommitted Points: 1741 More actions · Answer 7

And the winner is!!

No Converts - 6 secs

Converts - 7 secs

Both contain the same "From" clause with the missing link.

Removed the link on the no Converts - 21 secs.

Removed the link on the Converts - 19 Secs.

This is of course with selection criteria. Running wide open, both took around 5 minutes with the missing link... and returned A LOT of records, higher than this boy can count

Still running without the link......... I'd say that was the major cause of the problem. Both seem to return the same amount of time with/without converts.

This too was running from query analyzer , not creating a view. If you think the issue could be the view I could test that too by making a view with the converts

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 8

Remy? : )

quoted

Yes and no...

How can the converts be accountable for 97% of the work in the query (from 30 secs to 1)? I'm wondering if the missing link might not have been the real time saver here. I'm not arguing that doing nothing is faster than doing something .

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

This sounds plausible... but I wanted to be sure that this was the right answer .