December 28, 2011 at 7:39 am
Dev (12/28/2011)
ben.rosato (12/28/2011)
It seems to me that the need to send 2000+ parameters for an "IN" clause is the symptom of a poor design.Poor design of database, probably NO. It’s a bad design of 3rd party tool’s query / database engine.
There are plenty of questionable design practices going on in the piece of software.
December 28, 2011 at 7:45 am
GSquared (12/28/2011)
Dev (12/28/2011)
GSquared (12/28/2011)
If I'm reading it correctly, it's pretty much just a "Where in (values)" statement with as close to unlimited number of allowed values as they felt they could get. Intead of just a delimited list, or a TVF (which may or may not be an option if they have to maintain backwards compatibility beyond SQL 2008).Probably just some dev who felt that optional parameters were better than a list splitter. If he didn't know the high-performance methods of list splitting, and really needed to accept lists of 1000 or more values, then it almost makes sense. It would definitely perform better, once compiled, than a loop to split a 1000-value delimited string, including conversion from string values to bigint values and all that. If it's something that can compile once and keep a plan in the cache, then it might even be overall better than a delimited list version. Assuming, again, inefficient list parsing.
It's not fault of dev. It’s a tool generated query.
The tool was probably written by a dev somewhere along the way.
Then if your OS (assuming Windows) crashes, you can blame dev (somebody at Microsoft) as well. 😀
December 28, 2011 at 7:45 am
GSquared (12/28/2011)
If I'm reading it correctly, it's pretty much just a "Where in (values)" statement with as close to unlimited number of allowed values as they felt they could get. Intead of just a delimited list, or a TVF (which may or may not be an option if they have to maintain backwards compatibility beyond SQL 2008).Probably just some dev who felt that optional parameters were better than a list splitter. If he didn't know the high-performance methods of list splitting, and really needed to accept lists of 1000 or more values, then it almost makes sense. It would definitely perform better, once compiled, than a loop to split a 1000-value delimited string, including conversion from string values to bigint values and all that. If it's something that can compile once and keep a plan in the cache, then it might even be overall better than a delimited list version. Assuming, again, inefficient list parsing.
This is all dynamically generated and it has a different number of paramters so it's compiled every time.
December 29, 2011 at 6:23 am
ben.rosato (12/28/2011)
GSquared (12/28/2011)
If I'm reading it correctly, it's pretty much just a "Where in (values)" statement with as close to unlimited number of allowed values as they felt they could get. Intead of just a delimited list, or a TVF (which may or may not be an option if they have to maintain backwards compatibility beyond SQL 2008).Probably just some dev who felt that optional parameters were better than a list splitter. If he didn't know the high-performance methods of list splitting, and really needed to accept lists of 1000 or more values, then it almost makes sense. It would definitely perform better, once compiled, than a loop to split a 1000-value delimited string, including conversion from string values to bigint values and all that. If it's something that can compile once and keep a plan in the cache, then it might even be overall better than a delimited list version. Assuming, again, inefficient list parsing.
This is all dynamically generated and it has a different number of paramters so it's compiled every time.
Makes sense. In a sort of "I don't know what I'm doing here, so I'll just BF&I something together to solve it". (BF&I = "Brute Force & Ignorance")
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 6:26 am
There's not 1 stored procedure in the whole database either. NADA. There are a bunch of unused indexes though:-)
December 29, 2011 at 7:00 am
Dev (12/28/2011)
GSquared (12/28/2011)
Dev (12/28/2011)
GSquared (12/28/2011)
If I'm reading it correctly, it's pretty much just a "Where in (values)" statement with as close to unlimited number of allowed values as they felt they could get. Intead of just a delimited list, or a TVF (which may or may not be an option if they have to maintain backwards compatibility beyond SQL 2008).Probably just some dev who felt that optional parameters were better than a list splitter. If he didn't know the high-performance methods of list splitting, and really needed to accept lists of 1000 or more values, then it almost makes sense. It would definitely perform better, once compiled, than a loop to split a 1000-value delimited string, including conversion from string values to bigint values and all that. If it's something that can compile once and keep a plan in the cache, then it might even be overall better than a delimited list version. Assuming, again, inefficient list parsing.
It's not fault of dev. It’s a tool generated query.
The tool was probably written by a dev somewhere along the way.
Then if your OS (assuming Windows) crashes, you can blame dev (somebody at Microsoft) as well. 😀
Might. Depends on the situation. But this is a much closer relationship between result and dev than some line of code burried in Windows or some application that crashes it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 7:01 am
ben.rosato (12/29/2011)
There's not 1 stored procedure in the whole database either. NADA. There are a bunch of unused indexes though:-)
That's actually pretty normal. If you're using sp_prepexec, you don't usually also use SPs, in my experience.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 10:12 am
Please tell me the vendor is not Red Gate Software...
December 29, 2011 at 10:13 am
churlbut (12/29/2011)
Please tell me the vendor is not Red Gate Software...
No, not Red Gate.
December 29, 2011 at 11:19 am
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply