I'm running into an issue while using coalesce to create a dynamic where
clause. To give some background, here is the article that got me started on
this:
http://www.sqlteam.com/item.asp?ItemID=2077
What I am trying to accomplish is a series of user configurable filters that
will ignore a parameter if its not selected. So, let's presume there are 2
possible filters: "Location" and "Assigned To" for equipment that is "signed
out" to individuals. If the user selects a Location but not a person
(assigned to), then it should query only on LocationID. This should work
vice versa. And it should also work if they choose not select either.
Table EquipmentAssignments:
EquipmentID | LocationID | AssignedToID
--
1 1 2
1 1 1
2 2 1
2 NULL 2
2 2 NULL
So, based on the above mentioned article:
CREATE PROC ListEquipmentAssignments(
@.locationID int = NULL,
@.assignedToID int = NULL)
AS
-- note: in actual implementation all ID fields are actually
UNIQUEIDENTIFIERs
-- but it easier to read by using INTs
SELECT EquipmentID
FROM EquipmentAssignments
WHERE LocationID = COALESCE(@.locationID, LocationID) AND
AssignedToID = COALESCE(@.assignedToID, AssignedToID)
GO
This works great, except when dealing with the NULL column values. Because
of how COALESCE works, it returns NULL in the instances of those 2 records,
which won't evaluate to TRUE (and thus those records don't get returned).
I want to stay away from dynamically building SQL in the stored procedure.
Does anyone know of any other technique that could address this issue?Unfortunately, that method is absolutely terrible when it comes to
performance -- it forces a table scan. Why don't you want to use dynamic
SQL for this? You'll get MUCH better results.
There are other, non-dynamic methods with less performance problems, but
dynamic SQL really works best for these situations.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
> on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
> that
> will ignore a parameter if its not selected. So, let's presume there are
> 2
> possible filters: "Location" and "Assigned To" for equipment that is
> "signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
> Because
> of how COALESCE works, it returns NULL in the instances of those 2
> records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||http://www.sommarskog.se/dyn-search.html
> I'm running into an issue while using coalesce to create a dynamic where
> clause.|||IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
(location_id, 99))
Don't do that. You cannot validate or verify such non-relational
proprietary extensions.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
Joe,
Can other SQL engines (other than SQL Server, that is) use an index to
satisfy this kind of query?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||>> Can other SQL engines (other than SQL Server, that is) use an index to sa
tisfy this kind of query? <<
DB2 has some optimizer stuff now that looks at COALESCE() and builds
several execution plans (up to 16 or 32 of them as I recall) per
procedure, then it picks one based on the input parameters. It looks
for search conditions that can be reduced to (a = a) by a NULL and
drops them from consideration.
I am not sure about Ingres.|||see my Zero to N column
http://www.sqlservercentral.com/columnists/sholliday/
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:F09F7F73-D2F7-4A91-B596-B0A543E9AB49@.microsoft.com...
> I'm running into an issue while using coalesce to create a dynamic where
> clause. To give some background, here is the article that got me started
on
> this:
> http://www.sqlteam.com/item.asp?ItemID=2077
> What I am trying to accomplish is a series of user configurable filters
that
> will ignore a parameter if its not selected. So, let's presume there are
2
> possible filters: "Location" and "Assigned To" for equipment that is
"signed
> out" to individuals. If the user selects a Location but not a person
> (assigned to), then it should query only on LocationID. This should work
> vice versa. And it should also work if they choose not select either.
> Table EquipmentAssignments:
> EquipmentID | LocationID | AssignedToID
> --
> 1 1 2
> 1 1 1
> 2 2 1
> 2 NULL 2
> 2 2 NULL
> So, based on the above mentioned article:
> CREATE PROC ListEquipmentAssignments(
> @.locationID int = NULL,
> @.assignedToID int = NULL)
> AS
> -- note: in actual implementation all ID fields are actually
> UNIQUEIDENTIFIERs
> -- but it easier to read by using INTs
> SELECT EquipmentID
> FROM EquipmentAssignments
> WHERE LocationID = COALESCE(@.locationID, LocationID) AND
> AssignedToID = COALESCE(@.assignedToID, AssignedToID)
> GO
> This works great, except when dealing with the NULL column values.
Because
> of how COALESCE works, it returns NULL in the instances of those 2
records,
> which won't evaluate to TRUE (and thus those records don't get returned).
> I want to stay away from dynamically building SQL in the stored procedure.
> Does anyone know of any other technique that could address this issue?|||Give us a scalable solution that won't do a TABLE SCAN because this is a
MICROSOFT SQL SERVER forum.
Keep the DB2 solution on the DB2 forum, the Ingres solution on the Ingres
forum.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147830039.976788.281080@.j73g2000cwa.googlegroups.com...
> DB2 has some optimizer stuff now that looks at COALESCE() and builds
> several execution plans (up to 16 or 32 of them as I recall) per
> procedure, then it picks one based on the input parameters. It looks
> for search conditions that can be reduced to (a = a) by a NULL and
> drops them from consideration.
> I am not sure about Ingres.
>|||> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
table scan.
Can I have a list of clients you have recommended this too and a contact
name there so I can go and fix your lousy solution, this won't scale, it
will cause all sorts of blocking on a real system that has
update/insert/delete activity.
Tidy code does not mean efficient code.
You've been told several times this won't work well on SQL Server so why
continue to post this horrible, inefficient solution?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
> IFNULL (location_id, 99) = COALESCE(@.my_location_id, IFNULL
> (location_id, 99))
>
> Don't do that. You cannot validate or verify such non-relational
> proprietary extensions.
>
Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts
Monday, March 12, 2012
Question about Dynamic WHERE clause without Dynamic SQL statem
Well... It looks like the consensus on this is that its bad for performance
to use COALESCE in the manner I hoped to.
I was
on the comment about not using uniqueidentifiers. Not sure
what was meant by "you cannot validate or verify such non-relational
proprietary extensions". For some background, we use uniqueidentifiers
because some applications span physically disconnected networks that are
routinely synchonized using DataSets serialized as an XML files. I won't
bore ya'll with the details.
Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
tho. There is no reason why we can't have respectful conversations with eac
h
other on these newsgroups.
I do take offense to "tidy code does not mean efficient code". This is a
matter of analyzing your performance requirements against maintainability.
Tidy code is much more maintainable. Now, in this instance, the consensus i
s
that it is severely detrimental to performance. In this case, I would agree
on using a non-tidy efficient stored procedure. However, if the impact to
performance was only a couple clock ticks extra, I would go with tidy code
since the improvement in maintainability would far outweigh the minor
decrease to performance.
I thank you all for your inputs!
-Nate
"Tony Rogerson" wrote:
> This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
> table scan.
> Can I have a list of clients you have recommended this too and a contact
> name there so I can go and fix your lousy solution, this won't scale, it
> will cause all sorts of blocking on a real system that has
> update/insert/delete activity.
> Tidy code does not mean efficient code.
> You've been told several times this won't work well on SQL Server so why
> continue to post this horrible, inefficient solution?
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
>
>> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions".
What Celko meant is that if you add me to your database as a customer, and
assign me some system-generated GUID, then you add Tony, and assign him some
other GUID. If the database became corrupt, and you saw rows associated
with both of us and both of our GUIDs, how would you validate which GUID
truly belonged to who? Other than your own data store, there is nothing
that ties that GUID to me in a meaningful way.
Same argument goes for IDENTITY columns. If you don't remember that I was
assigned ID #455, would you be able to rebuild the database in the event of
a disaster, and still give me the same customer ID #?|||My tactlessness is directed 100% at --CELKO-- because of his condesending,
irgnorant and bad attitude towards other people on these forums, especially
newbies - because of that I treat him the same way he treats other people,
childish I know - but he's got to learn... i see it caused you offense so I
apologise, it was not directed at you.
Check out my blog entry for today on the COALESCE comparison based on this
and other posts on real test data :
http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
On my test its the difference between reading 40KBytes (dynamic SQL version)
and 104,248KBytes (coalesce version).
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
> performance
> to use COALESCE in the manner I hoped to.
> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>|||I still use this sort of code (coalesce) with simple lookup queries, where
the volume of data is small and the performance impact is minimal. Since
our databases are not hammered constantly the only real impact is that the
end user waits an extra few milliseconds. If the database had more
activity, then I would be more likely to use dynamic SQL. I do, however,
consider it more useful for the complex queries or reports, where
performance is more noticeable.
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
performance
> to use COALESCE in the manner I hoped to.
> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
is
> that it is severely detrimental to performance. In this case, I would
agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>
SQL|||Interesting stand point Aaron, that would assume the GUID or IDENTITY was
corrupted, what would prevent your other identifying keys from going the
same way?
Once assigned it should never be changed, IDENTITY does protect you that way
but using UNIQUEIDENTIFIER doesn't...
Personally, I prefer IDENTITY as the surrogate key because its more readable
than a 36 character hex number, IDENTITY is also fragmentation friendl(ier)
than the random nature of the GUID.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eYihBZbeGHA.3888@.TK2MSFTNGP02.phx.gbl...
> What Celko meant is that if you add me to your database as a customer, and
> assign me some system-generated GUID, then you add Tony, and assign him
> some other GUID. If the database became corrupt, and you saw rows
> associated with both of us and both of our GUIDs, how would you validate
> which GUID truly belonged to who? Other than your own data store, there
> is nothing that ties that GUID to me in a meaningful way.
> Same argument goes for IDENTITY columns. If you don't remember that I was
> assigned ID #455, would you be able to rebuild the database in the event
> of a disaster, and still give me the same customer ID #?
>|||> Interesting stand point Aaron, that would assume the GUID or IDENTITY was
> corrupted, what would prevent your other identifying keys from going the
> same way?
I'm not standing behind Celko's point, just clarifying it. But I do agree
that if your database got hosed, even if you still had all of my personal
information elsewhere, if you generated my primary key artificially (GUID,
sequence, identity, what have you) you'd be hard pressed to rebuild your
system and assign me the same primary key again. This is one reason we
strive to make sure the surrogate key remains internal -- a customer
shouldn't know his/her customer number if it's not something you can
validate and easily re-create if necessary. So to clarify further, I am not
against using surrogate keys, but I do understand why Celko and others find
them dangerous.
> Once assigned it should never be changed, IDENTITY does protect you that
> way but using UNIQUEIDENTIFIER doesn't...
> Personally, I prefer IDENTITY as the surrogate key because its more
> readable than a 36 character hex number, IDENTITY is also fragmentation
> friendl(ier) than the random nature of the GUID.
I agree wholeheartedly. We use GUIDs for other reasons, but we never make
them the key.|||> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
Wow, you think Tony lacks tact? First, his disrespect is aimed at exactly
one person, Celko. And if you spend more than 10 minutes here and witness
some of Celko's barraging against newbies, it's like comparing Lenin and
Barney.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
As with many other things, it really depends. I think what Tony objects to
is using tidy code because it is tidy, without regard to any of the other
criteria for sound development practices.
A|||The funny thing is, of the 6 or so posts of his that I have read today,
every one of them was very polite and informative, without any of the usual
bashing. It appears he is in a good mood, or perhaps is just trying a new
approach (maybe it is just me, but I think he has been more diplomatic
lately). Personally, I appreciate the discussions that his posts generate,
if not always the tact. I like to be reminded of the tradeoff between
portability, scalability, and concise code, and we do get some thoughtful
discussions out of it. I do feel sorry for new visitors to the group who
catch him on a bad day, however.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OPQF1ebeGHA.3808@.TK2MSFTNGP02.phx.gbl...
> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums,
especially
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL
version)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
sure
won't
a
maintainability.
consensus
to
code
a
contact
it
why
a
>|||I see now :) ... I just got done looking at some other threads and saw the
same pattern. One of my coworkers just informed me that he's seen CELKO
being less than respectful on other discussions. Sorry for my ignorance.
I probably should have said "I take exception" vice "offense" in my earlier
phrasing where I compared maintainability and performance. I wasn't
offended... just disagreed on the fact that the statement shouldn't be used
as a blanket statement, but should be expanded upon.
I'm just glad I can go somewhere and have SQL geniuses explain SQL to an SQL
dummy (me).
"Tony Rogerson" wrote:
> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums, especiall
y
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL versio
n)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
>
>|||Hi Nate,
I'm still laughing at Aaron comparing me and celko as Barney (me hopefully)
to Lenin - that is just so amusing.
There are a lot of fantastic people on these forums - I learn a lot here
myself, and believe me I'm the first one to admit I'm not a SQL genius -
things move too quickly and the product is just too big now.
Happy SQL'ing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:D887EF0B-C397-4309-98CB-1134B1689B08@.microsoft.com...
>I see now :) ... I just got done looking at some other threads and saw the
> same pattern. One of my coworkers just informed me that he's seen CELKO
> being less than respectful on other discussions. Sorry for my ignorance.
> I probably should have said "I take exception" vice "offense" in my
> earlier
> phrasing where I compared maintainability and performance. I wasn't
> offended... just disagreed on the fact that the statement shouldn't be
> used
> as a blanket statement, but should be expanded upon.
> I'm just glad I can go somewhere and have SQL geniuses explain SQL to an
> SQL
> dummy (me).
> "Tony Rogerson" wrote:
>
to use COALESCE in the manner I hoped to.
I was
on the comment about not using uniqueidentifiers. Not surewhat was meant by "you cannot validate or verify such non-relational
proprietary extensions". For some background, we use uniqueidentifiers
because some applications span physically disconnected networks that are
routinely synchonized using DataSets serialized as an XML files. I won't
bore ya'll with the details.
Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
tho. There is no reason why we can't have respectful conversations with eac
h
other on these newsgroups.
I do take offense to "tidy code does not mean efficient code". This is a
matter of analyzing your performance requirements against maintainability.
Tidy code is much more maintainable. Now, in this instance, the consensus i
s
that it is severely detrimental to performance. In this case, I would agree
on using a non-tidy efficient stored procedure. However, if the impact to
performance was only a couple clock ticks extra, I would go with tidy code
since the improvement in maintainability would far outweigh the minor
decrease to performance.
I thank you all for your inputs!
-Nate
"Tony Rogerson" wrote:
> This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
> table scan.
> Can I have a list of clients you have recommended this too and a contact
> name there so I can go and fix your lousy solution, this won't scale, it
> will cause all sorts of blocking on a real system that has
> update/insert/delete activity.
> Tidy code does not mean efficient code.
> You've been told several times this won't work well on SQL Server so why
> continue to post this horrible, inefficient solution?
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
>
>> I was
on the comment about not using uniqueidentifiers. Not sure> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions".
What Celko meant is that if you add me to your database as a customer, and
assign me some system-generated GUID, then you add Tony, and assign him some
other GUID. If the database became corrupt, and you saw rows associated
with both of us and both of our GUIDs, how would you validate which GUID
truly belonged to who? Other than your own data store, there is nothing
that ties that GUID to me in a meaningful way.
Same argument goes for IDENTITY columns. If you don't remember that I was
assigned ID #455, would you be able to rebuild the database in the event of
a disaster, and still give me the same customer ID #?|||My tactlessness is directed 100% at --CELKO-- because of his condesending,
irgnorant and bad attitude towards other people on these forums, especially
newbies - because of that I treat him the same way he treats other people,
childish I know - but he's got to learn... i see it caused you offense so I
apologise, it was not directed at you.
Check out my blog entry for today on the COALESCE comparison based on this
and other posts on real test data :
http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
On my test its the difference between reading 40KBytes (dynamic SQL version)
and 104,248KBytes (coalesce version).
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
> performance
> to use COALESCE in the manner I hoped to.
> I was
on the comment about not using uniqueidentifiers. Not sure> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>|||I still use this sort of code (coalesce) with simple lookup queries, where
the volume of data is small and the performance impact is minimal. Since
our databases are not hammered constantly the only real impact is that the
end user waits an extra few milliseconds. If the database had more
activity, then I would be more likely to use dynamic SQL. I do, however,
consider it more useful for the complex queries or reports, where
performance is more noticeable.
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
performance
> to use COALESCE in the manner I hoped to.
> I was
on the comment about not using uniqueidentifiers. Not sure> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
is
> that it is severely detrimental to performance. In this case, I would
agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>
SQL|||Interesting stand point Aaron, that would assume the GUID or IDENTITY was
corrupted, what would prevent your other identifying keys from going the
same way?
Once assigned it should never be changed, IDENTITY does protect you that way
but using UNIQUEIDENTIFIER doesn't...
Personally, I prefer IDENTITY as the surrogate key because its more readable
than a 36 character hex number, IDENTITY is also fragmentation friendl(ier)
than the random nature of the GUID.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eYihBZbeGHA.3888@.TK2MSFTNGP02.phx.gbl...
> What Celko meant is that if you add me to your database as a customer, and
> assign me some system-generated GUID, then you add Tony, and assign him
> some other GUID. If the database became corrupt, and you saw rows
> associated with both of us and both of our GUIDs, how would you validate
> which GUID truly belonged to who? Other than your own data store, there
> is nothing that ties that GUID to me in a meaningful way.
> Same argument goes for IDENTITY columns. If you don't remember that I was
> assigned ID #455, would you be able to rebuild the database in the event
> of a disaster, and still give me the same customer ID #?
>|||> Interesting stand point Aaron, that would assume the GUID or IDENTITY was
> corrupted, what would prevent your other identifying keys from going the
> same way?
I'm not standing behind Celko's point, just clarifying it. But I do agree
that if your database got hosed, even if you still had all of my personal
information elsewhere, if you generated my primary key artificially (GUID,
sequence, identity, what have you) you'd be hard pressed to rebuild your
system and assign me the same primary key again. This is one reason we
strive to make sure the surrogate key remains internal -- a customer
shouldn't know his/her customer number if it's not something you can
validate and easily re-create if necessary. So to clarify further, I am not
against using surrogate keys, but I do understand why Celko and others find
them dangerous.
> Once assigned it should never be changed, IDENTITY does protect you that
> way but using UNIQUEIDENTIFIER doesn't...
> Personally, I prefer IDENTITY as the surrogate key because its more
> readable than a 36 character hex number, IDENTITY is also fragmentation
> friendl(ier) than the random nature of the GUID.
I agree wholeheartedly. We use GUIDs for other reasons, but we never make
them the key.|||> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
Wow, you think Tony lacks tact? First, his disrespect is aimed at exactly
one person, Celko. And if you spend more than 10 minutes here and witness
some of Celko's barraging against newbies, it's like comparing Lenin and
Barney.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
As with many other things, it really depends. I think what Tony objects to
is using tidy code because it is tidy, without regard to any of the other
criteria for sound development practices.
A|||The funny thing is, of the 6 or so posts of his that I have read today,
every one of them was very polite and informative, without any of the usual
bashing. It appears he is in a good mood, or perhaps is just trying a new
approach (maybe it is just me, but I think he has been more diplomatic
lately). Personally, I appreciate the discussions that his posts generate,
if not always the tact. I like to be reminded of the tradeoff between
portability, scalability, and concise code, and we do get some thoughtful
discussions out of it. I do feel sorry for new visitors to the group who
catch him on a bad day, however.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OPQF1ebeGHA.3808@.TK2MSFTNGP02.phx.gbl...
> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums,
especially
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL
version)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
sure
won't
a
maintainability.
consensus
to
code
a
contact
it
why
a
>|||I see now :) ... I just got done looking at some other threads and saw the
same pattern. One of my coworkers just informed me that he's seen CELKO
being less than respectful on other discussions. Sorry for my ignorance.
I probably should have said "I take exception" vice "offense" in my earlier
phrasing where I compared maintainability and performance. I wasn't
offended... just disagreed on the fact that the statement shouldn't be used
as a blanket statement, but should be expanded upon.
I'm just glad I can go somewhere and have SQL geniuses explain SQL to an SQL
dummy (me).
"Tony Rogerson" wrote:
> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums, especiall
y
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL versio
n)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
>
>|||Hi Nate,
I'm still laughing at Aaron comparing me and celko as Barney (me hopefully)
to Lenin - that is just so amusing.
There are a lot of fantastic people on these forums - I learn a lot here
myself, and believe me I'm the first one to admit I'm not a SQL genius -
things move too quickly and the product is just too big now.
Happy SQL'ing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:D887EF0B-C397-4309-98CB-1134B1689B08@.microsoft.com...
>I see now :) ... I just got done looking at some other threads and saw the
> same pattern. One of my coworkers just informed me that he's seen CELKO
> being less than respectful on other discussions. Sorry for my ignorance.
> I probably should have said "I take exception" vice "offense" in my
> earlier
> phrasing where I compared maintainability and performance. I wasn't
> offended... just disagreed on the fact that the statement shouldn't be
> used
> as a blanket statement, but should be expanded upon.
> I'm just glad I can go somewhere and have SQL geniuses explain SQL to an
> SQL
> dummy (me).
> "Tony Rogerson" wrote:
>
Question about dynamic report
Hi:
I have a question of create a dynamic the report.
For example: At the beginning I have 3 columns in the report, build by VS. After couple weeks, the same report needs to have more columns. And i don't want to use the VS to modify the layout manually, instead, when adding new columns, I can call a function to modify the rdl file add attributes into the xml file automatically. But after that which way I can go to publish the modified report into the report server, then when user clicks on that report again, there will be more columns show up.
thanks
LuZ,
You can use the Reporting Services web service to publish the new report. See this link for documentation:
http://msdn2.microsoft.com/en-us/library/ms154697.aspx
-Ryan
Question about dynamic report
Hi:
I have a question of create a dynamic the report.
For example: At the beginning I have 3 columns in the report, build by VS. After couple weeks, the same report needs to have more columns. And i don't want to use the VS to modify the layout manually, instead, when adding new columns, I can call a function to modify the rdl file add attributes into the xml file automatically. But after that which way I can go to publish the modified report into the report server, then when user clicks on that report again, there will be more columns show up.
thanks
LuZ,
You can use the Reporting Services web service to publish the new report. See this link for documentation:
http://msdn2.microsoft.com/en-us/library/ms154697.aspx
-Ryan
Wednesday, March 7, 2012
Question about a SQL Reporting Tutorial Example
In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
In Data view, select the Employees dataset, and then use the generic
query designer to replace the original query with the following
expression:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
This expression
If I put that in the generic query designer it will not validate the
SQL.
Can anyone help with this?Expression-based queries cannot be validated and executed in the generic
query designer, because it does not evaluate the VB.NET expressions. They
are only evaluated at report runtime.
The general recommendation for dynamic queries using VB.NET expressions is
to start with a static query, then add a textbox to the report with the
dynamic query expression you want to use. Run the report to verify that the
expression-based commandtext evaluates to valid SQL, and then in the last
step replace the static query with the dynamic expression-based query. In
addition, the fields list is only auto-detected from non expression-based
queries.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0409051123.191910b6@.posting.google.com...
> In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
> In Data view, select the Employees dataset, and then use the generic
> query designer to replace the original query with the following
> expression:
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> This expression
> If I put that in the generic query designer it will not validate the
> SQL.
> Can anyone help with this?
In Data view, select the Employees dataset, and then use the generic
query designer to replace the original query with the following
expression:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
This expression
If I put that in the generic query designer it will not validate the
SQL.
Can anyone help with this?Expression-based queries cannot be validated and executed in the generic
query designer, because it does not evaluate the VB.NET expressions. They
are only evaluated at report runtime.
The general recommendation for dynamic queries using VB.NET expressions is
to start with a static query, then add a textbox to the report with the
dynamic query expression you want to use. Run the report to verify that the
expression-based commandtext evaluates to valid SQL, and then in the last
step replace the static query with the dynamic expression-based query. In
addition, the fields list is only auto-detected from non expression-based
queries.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0409051123.191910b6@.posting.google.com...
> In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
> In Data view, select the Employees dataset, and then use the generic
> query designer to replace the original query with the following
> expression:
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> This expression
> If I put that in the generic query designer it will not validate the
> SQL.
> Can anyone help with this?
Subscribe to:
Posts (Atom)