partial search performance

To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:

> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:

Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

Parse Return value of SYSTEM_USER

DECLARE @.UserName nvarchar(100)
SELECT @.UserName = SYSTEM_USER

value returned is "Domain\NTSignonName"
What I want is only "NTSignonName"

Is there a function to do this or an easy parse for this in SQL2000?

lqI know I can do:

DECLARE @.UserNameWithDomain nvarchar(100)
SELECT @.UserNameWithDomain = SYSTEM_USER

DECLARE @.UserNameNoDomain nvarchar(100)
SELECT @.UserNameNoDomain =
SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)

I'm hoping for something more aesthetic.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I know I can do:
> DECLARE @.UserNameWithDomain nvarchar(100)
> SELECT @.UserNameWithDomain = SYSTEM_USER
> DECLARE @.UserNameNoDomain nvarchar(100)
> SELECT @.UserNameNoDomain =
> SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)
> I'm hoping for something more aesthetic.

I don't know if it's more esthetic, but parsename() is an alternative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx

Parameters...HELP!

I am using 6 parameters in a report. If I set the Where clauses to OR,
the report will return everything (understood) but it doesnt work when
I set it to AND (how it should be). By themselves, each parameter
works. But when you combine all of them or even 2, nothing returns. Am
I missing a simple step?
Also, I wanted some parameters to be "optional"-dont have to have a
value in them (is this possible with report designer?). I've tried
setting the "optional" ones to null.
I even tried to use SQL profiler to try and view the sql being executed
but to no avail.
Any suggestions would be greatly appreciated!
Thanks!gte401:
Does the query work correct in Query Designer? Typically, I like to design
my SQL code there and then paste it into the report query after it's been
debugged. If it's working correctly then you should get the same results.
As for six parameters, I have a few with up to four with no problems.
Regarding your question about leaving parameters NULL. If you do, then
you'll definitely want to make sure you've accommodated the NULL in your
WHERE statement. You could use an ISNULL function to convert it to '%' and
do a LIKE. For example:
WHERE myField LIKE '%' + RTRIM(LTRIM(ISNULL(@.TEST,''))) + '%'
Hope this helps! I'm sure someone may have an easier way to do this, but
for now it works for me.
Patrick|||Open the report up in Visual Studio.
Go to Report => parameters.
You can specify if the value of a parameter may be NULL/Empty.
hope this helps
Regards,
Stas K.|||When you select the Null Function for say Last Name, does the query
skips this parameter or does it look for Last Names with Null?
Thanks
Kevin|||Patrick,
My SQL runs. Its just when I add the Where clause, it does not work.
This is what Business Intelligence Studio generated:
WHERE
(Person.Status LIKE @.Status) AND (PersonType.PersonType_Desc =PersonType.PersonType_Desc) AND (Methodology.Methodology_Desc IN
(@.Methodology_Desc)) AND (Certification.Certification_Desc IN
(@.Certification_Desc)) AND (Person.Last_Name = @.Last_Name) AND
(Skill.Skill_Desc IN (@.Skill_Desc)) AND (Person.First_Name =@.First_Name)
When you run it, it will prompt "Parameters for Query" I set everything
to null.
I then go to the preview tab. Enter the desired values in my search
page, and nothing appears.|||This also works:
and myfield = ISNULL(@.myparameter, myfield)
... if @.myparameter isnull then statement is myfield = myfield, which is
always true
"gte401e" wrote:
> I am using 6 parameters in a report. If I set the Where clauses to OR,
> the report will return everything (understood) but it doesnt work when
> I set it to AND (how it should be). By themselves, each parameter
> works. But when you combine all of them or even 2, nothing returns. Am
> I missing a simple step?
> Also, I wanted some parameters to be "optional"-dont have to have a
> value in them (is this possible with report designer?). I've tried
> setting the "optional" ones to null.
> I even tried to use SQL profiler to try and view the sql being executed
> but to no avail.
> Any suggestions would be greatly appreciated!
> Thanks!
>|||Dterrie,
Thanks for the help!
But one more question....I have several multi value parameters
=IN(@.Example) . I know you cant set them to Null. Is there a way to
make them optional though? Or can I incorporate =IN(@.Example) into the
statement you gave me? If so, how?
Thanks in advance!
Kevin

Parameters best practice

Folks, What is the recommended best practice for the following?
Let’s say we are in the pubs database and we want to return some employee
information via a stored proc with an optional parameter of last name. The
issue at hand is how do you structure the stored proc to allow multiple last
names to be provided? It seems that some form of
SELECT * FROM [employee] WHERE [lname] in (@.lastNames)
would do the trick, but no.
Building a sql statement will work but that kind of defeats the value of
using parameters to prevent injection.
One and none provided are easy. Executing the stored proc multiple times
does not seem like the best answer.
Thanks…ChucIt seems as if I quote Dejan on a daily basis:
http://solidqualitylearning.com/blo.../10/22/200.aspx
Since the function mentioned in Dejan's blog returns a table of values,
you'd use it in a join to your table.
ML|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Why do you think that your imaginary syntax "lname IN (@.lastNames"
makes sense? A parameter is scalar, SQL is a compiled language!! Why
did you use square brackets instead of non-proprietary syntax?
names to be provided? <<
This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
w of any language class.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter;
it is not code. This is not just an SQL problem; this is a basic
misunderstanding of programming of principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
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 outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
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):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
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.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
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 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.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
MS comes up to standards that a over a decade old.
3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.|||Great attitude. Check it at the door next time...
"--CELKO--" wrote:

> Why do you think that your imaginary syntax "lname IN (@.lastNames"
> makes sense? A parameter is scalar, SQL is a compiled language!! Why
> did you use square brackets instead of non-proprietary syntax?
>
> names to be provided? <<
> This is a common Newbie question. This shows that you don't know what
> a scalar parameter is. This is MUCH worse than not knowing SQL. This
> is a fundamental programming concept that you should learn in the first
> w of any language class.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter;
> it is not code. This is not just an SQL problem; this is a basic
> misunderstanding of programming of principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> 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 outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> 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):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> 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.
> You can then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> 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 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.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array", like this in SQL/PSM
> (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM ( VALUES (p1), (p2), .., (pN))
> AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
> MS comes up to standards that a over a decade old.
> 3) The right way! You load the Parmlist table with values so that each
> value is validated by the SQL engine, subject to more constraints and
> you have no SQL injection problems. A good optimizer will not need the
> SELECT DISTINCT, just a SELECT.
>

Parameterized Query Question

I am trying to use the following SQL query to return a set of values:

SELECT id, submit_date, company_name, request_type, status
FROM tblRequestForms
WHERE request_type IN (@.RequestType) AND status IN (@.Status)
ORDER BY id ASC

I have tried passing an array of string values to both @.RequestType and @.Status, but It does not work. Is there any way to pass multiple values like this using parameters?

Thanks,
AaronI wish it were that simple, but alas it's not.

This has been discussed on several threads. User Ghan has pretty much summed up the different ways you can accomplish this in this post:view post 308485

Terri