The Temp Table blues OR The Dynamic SQL blues
roy.@nderson@gm@il.com
I'm on the right track, let me know as well. :) Basically I'm a web dev
creating a page that will search through almost 5 million records, so
speed is important. The users will be able to search via 15 different
criteria. Due to this potential, dynamically building the query string
is the only sensible solution to me. I built my page with a d-sql SP in
the backend and all is well. Then my boss throws me a curve ball. He
wants users to be able to dump in multiple "equipment" numbers into one
of the text boxes, separated by commas, and have the page search via
that as well.
My solution (with the other 14 search criteria's screened out) is
below. Is there any way to improve this method? I can't use a table
variable because it's inaccessible when the d-sql is executed. Thanks.
CREATE PROCEDURE itv_history
@equipment nvarchar(250)
AS
SET DATEFORMAT mdy
CREATE TABLE #ContTable
(
ContValue nvarchar(15)
)
DECLARE @sql nvarchar(2000)
SET @sql = N'SELECT * FROM dbo.ITVD '
if @equipment <> ''
BEGIN
declare @separator nvarchar(1)
set @separator = ','
declare @separator_position int
declare @array_value nvarchar(15)
set @equipment = @equipment + ','
while patindex('%,%' , @equipment) <> 0
begin
select @separator_position = patindex('%,%' , @equipment)
select @array_value = left(@equipment, @separator_position - 1)
Insert #ContTable
Values (ltrim(rtrim(@array_value)))
select @equipment = stuff(@equipment, 1, @separator_position, '')
end
SET @sql = @sql + ' fvd INNER JOIN #ContTable a ON fvd.equipment LIKE
'+'''%'''+'+a.ContValue+'+'''%''')
END
execute (@sql)
GO
roy.@nderson@gm@il.com
> SET @sql = @sql + ' fvd INNER JOIN #ContTable a ON fvd.equipment LIKE
> '+'''%'''+'+a.ContValue+'+'''%''')
>
> END
>
> execute (@sql)
>
> GO
And actually...the inner join is wrong. It returns ALL records. What I
was trying to accomplish there was a wildcard search (as many users
will just enter in the first or last half of the "equipment" number).
roy.@nderson@gm@il.com
parsing just keep looking clunkier and clunkier?
if @equipment <> 'z'
BEGIN
declare @separator nvarchar(1)
set @separator = ','
declare @separator_position int
declare @array_value nvarchar(15)
set @equipment = @equipment + ','
while patindex('%,%' , @equipment) <> 0
begin
select @separator_position = patindex('%,%' , @equipment)
select @array_value = left(@equipment, @separator_position - 1)
if ltrim(rtrim(@array_value))<>''
begin
Insert #ContTable
Values (ltrim(rtrim(@array_value)))
end
select @equipment = stuff(@equipment, 1, @separator_position, '')
end
SET @sql = @sql + ' fvd INNER JOIN #ContTable ct ON fvd.equipment LIKE
'+'''%'''+'+ ct.ContValue +'+'''%''')
END
execute (@sql)
Anith Sen
http://www.sommarskog.se/arrays-in-sql.html
and
http://www.sommarskog.se/dyn-search.html
--
Anith
Erland Sommarskog
> Hey all, I need some suggestions (if any) for improvement. If you think
> I'm on the right track, let me know as well. :) Basically I'm a web dev
> creating a page that will search through almost 5 million records, so
> speed is important. The users will be able to search via 15 different
> criteria. Due to this potential, dynamically building the query string
> is the only sensible solution to me. I built my page with a d-sql SP in
> the backend and all is well. Then my boss throws me a curve ball. He
> wants users to be able to dump in multiple "equipment" numbers into one
> of the text boxes, separated by commas, and have the page search via
> that as well.
Oh-oh! Beware that with that sort of user interface, you will get a tough
job to handle bad input. (Two commas in a row, comma missing, and what
if there is a single quote in the middle of it.) I would investigate the
possibilities for a better user interface.
> My solution (with the other 14 search criteria's screened out) is
> below. Is there any way to improve this method? I can't use a table
> variable because it's inaccessible when the d-sql is executed. Thanks.
Use a table-valued function to unpack a comma-separated list. See the links
Anith posted.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
Another option is to use the fact that T-SQL can handle over 1000
parameters. This is almost certainly large enough -- heck, 100 is
probalby more typing than anyone wants to do! The code is easy to
write with "cut & paste" editing. Now, if you are lucky, parameter
sniffing might help execution.
SELECT .. FROM .. WHERE parm IN (@p001, @p002,.. , @p100)
Trying to write even a simple parser in SQL is not a good idea-- that
is a good job for 'C' or assembly languages. Is a pair of commas a
zero or a NULL? What do you do with alphas in the string? What about
the stray single quote? Minus signs in the front or the back of a
substring? Decimals -- error, rounded or accepted? Ditto FLOAT and
REAL? Did we squeeze out spaces or not?
Tony Rogerson
> parameters. This is almost certainly large enough -- heck, 100 is
> probalby more typing than anyone wants to do! The code is easy to
> write with "cut & paste" editing. Now, if you are lucky, parameter
> sniffing might help execution.
>
> SELECT .. FROM .. WHERE parm IN (@p001, @p002,.. , @p100)
Are you for real? Have you actually put something like this into production
or are you making it up as you go along (gee I know the answer to that
already).
Would you like to compare two solutions - how you would code using
'portable' standard SQL and how we'd do it in the real world just to see
what problems you actually get out in the field - you never know, you might
learn something!
>
> Trying to write even a simple parser in SQL is not a good idea-- that
> is a good job for 'C' or assembly languages. Is a pair of commas a
> zero or a NULL? What do you do with alphas in the string? What about
> the stray single quote? Minus signs in the front or the back of a
> substring? Decimals -- error, rounded or accepted? Ditto FLOAT and
> REAL? Did we squeeze out spaces or not?
>
Ohhh you mean CLR....
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1150481591.6...@p79g2000cwp.googlegroups.com...
Mike C#
won't be able to use indexes properly with that type of WHERE clause. OK,
so enough of that. Here's my idea:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
CREATE FUNCTION dbo.ParseDelimitedList (@list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@list, 1,
CASE WHEN CHARINDEX(',', @list, Number + 1) > 0 THEN
CHARINDEX(',', @list, Number + 1) - 1
ELSE LEN(@list) - CHARINDEX(',', @list, Number + 1)
END)
ELSE SUBSTRING(@list, Number + 1,
CASE WHEN CHARINDEX(',', @list, Number + 1) > 0 THEN
CHARINDEX(',', @list, Number + 1) - Number - 1
ELSE LEN(@list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@list, Number, 1) = ',' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
-- Here we create the SP to retrieve the relevant data.
CREATE PROCEDURE dbo.itv_history @equipment NVARCHAR(250)
AS
CREATE TABLE #ContTable
(
ContValue NVARCHAR(15)
)
INSERT INTO #ContTable (ContValue)
SELECT Value
FROM dbo.ParseDelimitedList(@equipment)
SELECT * -- Replace this * with relevant column names
FROM dbo.ITVD i
INNER JOIN fvd.equipment e
ON i.equipmentnumber LIKE i.ContValue + '%' -- Remove leading % to improve
performance
GO
"roy.@nderson@g...@il.com" <roy.an...@gmail.com> wrote in message
news:1150380302.2...@h76g2000cwa.googlegroups.com...
--CELKO--
procedureal and proprietary code:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the inputs, in the form of the
original key column and one parameter per row.
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
CREATE VIEW ParmList (keycol, place, parm)
SELECT keycol,
COUNT(S2.seq), -- reverse order
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. The relative postion of each element
in the list is given by the value of "place", but it does a count down
so you can plan horizonal placement in columns.
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks, handle double
commas and non-numerics in the strings, take care of floating point and
decimal notation, etc. Basically, you must write part of a compiler in
SQL. Yeeeech! Or decide that you do not want to have data integrity,
which is what most Newbies do in practice altho they do not know it.
Mike C#
> Same idea, more portable and less nested code in a VIEW to avoid
> procedureal and proprietary code:
>
Thanks for your input. To keep things on target, the OP did not specify the
need to port code to Postgres, Informix, Oracle or any other database
platform besides SQL Server. The OP posted to
microsoft.public.sqlserver.programming, and submitted Transact-SQL code he
had been working on. This leads to the belief that the OP wants a Microsoft
SQL Server solution. Not a MySQL or DB2 solution. The OP also implied he
wanted to use a stored procedure to return code. The only thing
"procedureal" about this code is the Stored Procedure definition. "Stored
Procedures", by definition are "procedureal". Stored "Procedure" is
apparently not just a clever title.
> This will be the table that gets the inputs, in the form of the
> original key column and one parameter per row.
>
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
Now what in the world is a SQL Server programmer going to do with a line
like this?
SUBSTRING(',' || I1.input_string || ',' FROM S1.seq FOR 1) = ','
Sit back and watch the errors fly or spend a bunch of time time trying to
figure out what this exactly is supposed to mean in T-SQL, that's what.
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks, handle double
> commas and non-numerics in the strings, take care of floating point and
> decimal notation, etc. Basically, you must write part of a compiler in
> SQL. Yeeeech! Or decide that you do not want to have data integrity,
> which is what most Newbies do in practice altho they do not know it.
You could write the ultimate non-kludge solution (of which I don't count
this as one - see Erland Sommarskog's pages here
http://www.sommarskog.se/arrays-in-sql.html and here
http://www.sommarskog.se/dyn-search.html for some tips), but if it's not
written in the local language what good does it do anyway?
BTW, appending commas to the beginning and end of the input string is not
how a compiler parses tokens (a good compiler anyway). A compiler parses
tokens one at a time, without modifying the input string. You already know
where the beginning and ending of the input string occurs, why do you feel
the need to modify it? I consider this to be a First Order Kludge.
Mike C#
news:1150565763.8...@f6g2000cwb.googlegroups.com...
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
>
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
BTW, I'm surprised to see you advocating use of such an awful denormalized
table for anything. I've read your books so I know you are aware of the
definition of First Normal Form. I also don't need to explain why this
table is the absolute worst example I've ever seen anyone with your
experience present as a solution to anyone, much less a SQL newbie.
I'd definitely be interested to see the kludge you come up with on the
front-end that comes up with 'first', 'second', etc. to relate to an
individual query of a user on a website. I can just see website user
#9,999's keycol: "ten thousand nine hundred and ninety-ninth". Seems
you're going to have to supply more than a CHAR(10) to hold that kludgy
primary key. And don't let multiple users query multiple times
simultaneously. The kludge for creating a primary key using your method
would be ridiculous. Of course that doesn't matter if you believe that a
SQL database sits in a universe unto itself, and doesn't need to interact
with other parts of a system like websites. That might have been the case
back in '75, but it's a whole new century.
--CELKO--
I am not advocating it; I am apologizing for it. I even admit this is
a kludge!
>> The kludge for creating a primary key using your method would be ridiculous. <<
That is why it is sample code and not a production system. In fact, I
just gave a key without says how to get it, so we could get data to run
thru the VIEW.
Mike C#
Why even post such an awful kludge in the name of "portability", when the
net result is a demonstration of "how not to do it"? Learning the right way
on one platform is hard enough without having to navigate poorly written
solutions that won't even run on your platform in the process. To put it
simply, this thing is horrendous. I don't believe the OP was looking for
the academic ANSI Standard "detached-from-reality" pseudo-code that would
kludge its way along after hours of additional tinkering and conversion.
Based on his post he's looking for a SQL Server solution, in SQL Server's
native tongue, that he can incorporate into a production environment.
Anyone can post pseudo-code in the name of academia, the OP here was asking
for a real solution to a real problem on a real platform.
--CELKO--
If you are a reporter who writes for a readership, you have the choice
of writing in Standard English, a local dialect or even making up your
language on the fly. You should use Standard English. Did your editor
say to do this? Did he have to say it? Geesh, if you need to be told
that, you are the worst journalist they hired.
Likewise, a competent programmer will write readable program in a
standard language.
>> The OP posted to microsoft.public.sqlserver.programming, and submitted Transact-SQL code he had been working on. This leads to the belief that the OP wants a Microsoft SQL Server solution. Not a MySQL or DB2 solution. <<
"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898
Giving an exclusively MySQL or DB2 or other dialect answer would be as
wrong as a T-SQL solution *for the same reasons*; what you want is a
Standard SQL answer.
>> The OP also implied he wanted to use a stored procedure to return code. The only thing "procedural" about this code is the Stored Procedure definition. <<
Stored procedures have to be invoked, while VIEWs do not. And I am
willingto bet that the parser hidden in that function call has a loop.
>> Now what in the world is a SQL Server programmer going to do with a line like this? <<
The same thing a Hill Billy does when he reads the newspaper;
understand the Standard dialect and convert "isn't" to "ain't" if he
needs to.
>> You could write the ultimate non-kludge solution (of which I don't count this as one - see Erland Sommarskog's pages ... ), but if it's not written in the local language what good does it do anyway? <<
Because well-written code will run in the local dialect, as well as be
readable to future prtogrammers maintaining the code and run on other
platforms You are making a false dicotomy of dialect or Standard.
They overlap a lot.
You need a very good reason to use dialect. Being too lazy to type
CAST(), CURRENT_TIMESTAMP, MONEY, etc. in favor of dialect for the same
functionality is not a reason.
>> BTW, appending commas to the beginning and end of the input string is not how a compiler parses tokens (a good compiler anyway). <<
I taught a two-quarter intro to compilers. What we have posted is
nowhere near a compiler! They will accept anything for input and there
is no mechanism for error reporting. This is string manipulations, not
real parsing which would have "accepted" and "rejected" terminal
states.
>> A compiler parses tokens one at a time, without modifying the input string. <<
LOL! You never wrote PL/I and do not remember VDL. The language was
so complex that it took three passes to parse a program. One of the
insanities was that there were no reserved words, so you could "DECLARE
declare INTEGER" and it would try to correct any syntax errors for you.
It was only slightly better than ADA, but that is another "old fart
war story".
PL/I did this correction and guessing by adding sentinels to names (I
think that was the term -- too many years have past). These were like
the @ sign in T-SQL and were used by the next pass of the compiler to
get the type of the token -- like converting the prior example to
"DECLARE @declare INTEGER" in T-SQL terms. Certain combinations of
characters were replaced, such as ".GT." to ">" and stored in the
"source code" for the next pass if the character set allowed them.
You had a choice of "original" or "as parsed" source code. The
original code was of little or no use because of the changes.
>> You already know where the beginning and ending of the input string occurs, why do you feel the need to modify it? I consider this to be a First Order Kludge. <<
To make the code easier to write and maintain. To avoid extra calls to
functions, and more complex math.
Mike C#
<<If you are a reporter who writes for a readership, you have the choice
> of writing in Standard English, a local dialect or even making up your
> language on the fly. You should use Standard English. Did your editor
> say to do this? Did he have to say it? Geesh, if you need to be told
> that, you are the worst journalist they hired.>>
If that is the case, then why isn't International English ("The Queen's
English") used by the greatest journalists they hired in the United States.
Probably because they, unlike the worst journalists, write for their target
audience. I'm sure your editor once told you that. Just as a hint, you can
tell your target audience in this instance by the name of the newsgroup:
and it's not "public.generic.academic.ansi.sql.programming" or
"ansi.sql.mental.m@sturb@tion".
<<"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
> customs of his tribe and island are the laws of nature." - Caesar and
> Cleopatra; George Bernard Shaw 1898>>
"And since you know you cannot see yourself
So well as by reflection, I, your glass,
Will modestly discover to yourself
That of yourself which you yet know not of." - Julius Caesar; Shakespeare
<<Giving an exclusively MySQL or DB2 or other dialect answer would be as
> wrong as a T-SQL solution *for the same reasons*; what you want is a
> Standard SQL answer.>>
What you want in a Microsoft SQL Server newsgroup is an answer which you can
use on Microsoft SQL Server; not an answer that works on an imaginary ANSI
Server. Giving an Olde English answer in a 21st century English newspaper
is quaint, but doesn't do much to help those who asked the question unless
they have translation materials handy.
<<Stored procedures have to be invoked, while VIEWs do not. And I am
> willingto bet that the parser hidden in that function call has a loop.>>
LMMFAO. Please feel free to review the code and "Show Me the Loop." If you
look at it you will see it is an INLINE USER-DEFINED FUNCTION, which is
defined by Microsoft SQL Server as only capable of containing and returning
the result of a SINGLE SELECT STATEMENT. So how much do you care to wager
on that? I'd love to take your money all day long!
<<The same thing a Hill Billy does when he reads the newspaper;
> understand the Standard dialect and convert "isn't" to "ain't" if he
> needs to.>>
The same thing the average American does when the reporter writes a
newspaper article in Latin simply to make himself feel better about his
wealth of knowledge. Either spend a bunch of time trying to translate it to
something useful in the real world, or simply ignore it.
<<Because well-written code will run in the local dialect, as well as be
> readable to future prtogrammers maintaining the code and run on other
> platforms You are making a false dicotomy of dialect or Standard.
> They overlap a lot.>>
I see what you're getting at here: Since your sample code does not even run
in the local dialect, it won't be used. Obviously nothing could be easier
for future programmers to maintain than no code at all. Is that an accurate
synopsis? There are too many differences between platforms to try to stick
strictly to the "write once, run anywhere" model. Sure it makes a wonderful
academic exercise and I'm sure many PhD's have been granted based on
research into the topic, but bottom line is it does not work in the real
world, where the rest of us are forced to live.
I once read something to the effect that when the ANSI standard does not
contain a standard way of doing something, that you should use what you have
available and be sure to document it well. I believe the author of that
piece was you. If ANSI does not contain a standard way of creating a
Parameterized View, then why should I suffer through denormalized kludges to
fake it when I have the ability to create such a thing on my platform?
That's the definition of hard-headed hill-billy for you.
<<You need a very good reason to use dialect. Being too lazy to type
> CAST(), CURRENT_TIMESTAMP, MONEY, etc. in favor of dialect for the same
> functionality is not a reason.>>
You are presenting a false "dichotomy" here and attempting to change the
subject at the same time. No one here is disputing CAST(),
CURRENT_TIMESTAMP, MONEY, etc. '+' is the concatenation operator used by
SQL Server for strings. What good is '||' to a newbie programmer in a SQL
Server Newsgroup when you don't even bother to explain WTH it is? On SQL
Server it's nothing more than an Error Message.
>>> BTW, appending commas to the beginning and end of the input string is
>>> not how a compiler parses tokens (a good compiler anyway). <<
>
<<I taught a two-quarter intro to compilers. What we have posted is
> nowhere near a compiler!.>>
And I've taken classes on compiler design. Whoop dee. Make up your mind:
either what we have posted here is "nowhere near a compiler!" or what we
have posted here is "part of a compiler in SQL. Yeeeech!"
<<LOL! You never wrote PL/I and do not remember VDL. The language was
> so complex that it took three passes to parse a program. >>
Like I said, a "Good Compiler". PL/I sounds like a kludgy piece of crap the
way you describe it. Fortunately I never had to write PL/I, so I didn't
have to unlearn a bunch of kludgy hacks to get the job done the right way.
Of course we're not talking about the number of passes here (at least I'm
not). Back in the day Pascal, C and most other compilers were multi-pass as
well. The solution here which tokenizes the comma-delimited string does not
use three passes, it uses a single set-based "pass". I don't know what the
number of passes has to do with anything, or what your point is here, but
let's continue...
<<PL/I did this correction and guessing by adding sentinels to names (I
> think that was the term -- too many years have past). These were like
> the @ sign in T-SQL and were used by the next pass of the compiler to>>
...
<<You had a choice of "original" or "as parsed" source code. The
> original code was of little or no use because of the changes.>>
And as you point out it was a bunch of garbage. So what is the point of
modifying the input string...
<<To make the code easier to write and maintain. To avoid extra calls to
> functions, and more complex math.>>
As far as "more complex math"? This is not calculus; nor even
multiplication and division. It's simple addition and subtraction. What in
the world could be less complex? If you want something truly less complex
and easier to maintain than the CASE logic required in the SELECT statement,
then a simple procedural loop will do the trick as well as minimize the
number of function calls.
Tony Rogerson
>>> like this?
>>>
Interesting you never answer this question celko!
And this is a MICROSOFT SQL SERVER group NOT a STANDARD SQL PORTABILITY
group - if you want that go make your own, there will not be much traffic.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1150578156.3...@c74g2000cwc.googlegroups.com...