mahima


Hi,

I have one stored procedure which has the parameters in the following format

create procedure GetSpecies

@species varchar(100)='1,2,3,4,-1' here -1 is for null value selected from the report.

@Gender varchar(100)='2,3,4,-1'

as

Select .. from

cacase c

Left join Patient p on p.caCaseid=c.caCaseid

some joins here

Where p.speciesID in(Select * from dbo.getCSV(@Species))

And p.GenderID in(Select * from dbo.getCSV(@Gender))

Where as GetCSV is a table valued function which takes the Comma separated string and didvided it into individual parts and insert into table from this TBV I am returning the table which I am using in the Where clause of the above select statement.

Here my question is if we call the GetCSV tbv in the above where clause it will be called for each SpeciesID of the Patient table.If PaPatient table has 1000 records it will be called that 1000 time i think.

Can we do like this before the above Select statement.

Declare @t table

Select @tSpec= Select * from dbo.GetCSV(@Species)

and use this @TSpec in the where clause of the above select statement.

same for Gender also.

Thanks in advance.




Re: Table valued function

Kent Waldrop 2007 Mar


Mahima:

I hear what you are asking; but I think it woud be better to do an EXISTS search than an IN search. I think that the EXISTS is faster than IN. Something like:

Where exists ( select 0 from getCSV(@species) x where p.speciesID = x.CsvValue )

and exists ( select 0 from getCSV(@Gender) y where p.GenderID = x.CsvValue )

At the moment I don't see a need to send these values into a table unless you are going to use these table variables multiple times. Is this an issue of trying to improve performance






Re: Table valued function

Mahima

Hi Kent,

Thanks for your answer.

select * from

cacase c

join paPatient on ..

Where exists ( select 0 from getCSV(@species) x where p.speciesID = x.CsvValue

but whether the GetCSV is called for each p.paSpeciesID in paPatient if this is the case I think this effects the performance In our patient table there are million number of rows exist. or how many times that getCSV function is called in this scenario.

Like @Species we have 5 variables coming from the report calling getCSV for each variable effects the prformance.

Or without the tablevalued function can we do like this:

for each variable, extract the string from CSV and store i the table and use the table in where clause.

DECLARE @t TABLE (col1 VARCHAR(100))
DECLARE @separator_position INT
,@array_value VARCHAR(1000)
,@Array VARCHAR(1000)
,@separator CHAR(1)
--Set @Array='1,2,3,4,Null,0'
Set @Array='2,3'
Set @separator=','
--For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @array = @array + @separator
-- patindex matches the a pattern against a string
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT INTO @t SELECT @array_value
-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
Select * from @t.
The above coding is necessary for each variable of total 5 variables.
Thats why I have written the above coding in a TBV function and using the table in Where clause.

Where exists ( select 0 from getCSV(@species) x where p.speciesID = x.CsvValue

but whether the GetCSV is called for each p.paSpeciesID in paPatient if this is the case I think this effects the performance In our patient table there are million number of rows exist. or how many times that getCSV function is called in this scenario.

Thanks in advance





Re: Table valued function

Kent Waldrop 2007 Mar

Mahima:

Is it possible for you to used a blocked list instead of a comma delimited list I have found that in many cases a blocked list can run many times faster than a delimited list.

Kent





Re: Table valued function

Mahima

Hi Kent,

The Comma separated list as a parameter to the Stored procedure is coming from the SQL server report this i can't able to change.Can you please tell me what is blocked list.Any other approach other than writing the block of code containg While loop to separate CSV string for each parameter.

Thanks in advance.





Re: Table valued function

Kent Waldrop 2007 Mar

Mahima:

Here is an example of usage of blocked lists and a comparison between blocked lists and delimited lists. I am not sure if you can try to apply this or not:

-- ------------------------------------------------------------------
--
-- Overview:
--
-- One way that is sometimes seen to store lists in Transact SQL
-- is to store the list as a string of characters with each
-- element of the list separate each member of the list with
-- a comma or some other delimiting character. For example, the
-- list of the first five numbers might be stored as this comma
-- separated list:
--
-- '1,2,3,4,5'
--
-- This string can be processed by a table valued function to
-- return a list of the desired five integers.
--
-- An alternative approach is to store this same list in as
-- characters in a more rigidly structured arrangement. For
-- example we may choose to store the same list of five numbers
-- as 3-character string segments in a single string. In this
-- case I will right-justify this string as:
--
-- ' 1 2 3 4 5'
--
-- A major advantage of this approach is that we no longer need
-- to define a function to process our list of numbers; we can
-- simply us the SQL Server SUBSTRING builtin function. By
-- avoiding the overhead of a user defined function we will be
-- able to process our lists much more efficiently.
--
-- For example, if we want to assign the nth element of our
-- list to an integer variable named @TARGET we can access the
-- nth element with an integer variable named @n with something
-- like:
--
-- DECLARE @Target INTEGER
--
-- SET @Target
-- = CONVERT(INT, SUBSTRING(' 1 2 3 4 5', 3*@n-2, 2))
--
-- The block size can be set to different sizes to assure that
-- each substring will be large enough to hold any potential
-- list member. Another potential alternative is to store the
-- data as blocked binary instead of blocked characters.
--
-- In the example that follows, the DELIMITED code is commented
-- out. To run the DELIMITED code instead of the BLOCKED code
-- just uncomment the DELIMITED code and convert the BLOCKED
-- code into comments.
--
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--
-- A comparison between DELIMITED versus BLOCKED string lists.
--
-- The delimited string lists are interpreted by Jens Suessmeyer's
-- split function that can be found here:
--
-- From: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=326300&SiteID=1
--
-- In this quick test the DELIMITED string lists take 16.7 times
-- longer to process than the BLOCKED string lists.
The long and
-- short is that BLOCKED string lists tend to be WAY faster than
-- DELIMITED string lists.
--
-- There are a couple of good writeups from MVP Erland Sommarskog
-- that discuss arrays and lists in SQL Server:
--
-- From: http://www.sommarskog.se/arrays-in-sql-2005.html
-- and: http://www.sommarskog.se/arrays-in-sql-2000.html
--
-- NOTE: In this tests the segments are sprayed out over different
-- rows in the target table. This kind of test is more
-- favorable to DELIMITED string lists than a test in which
-- the results are sprayed out over columns instead of rows.
-- In such a test the difference between BLOCKED and
-- DELIMITED string lists balloons up to about an 85 to 1
-- ratio in favor of using BLOCKED string lists.
--
-- This mockup makes use of a SMALL_ITERATOR table and the
-- RAND() scalar function. These two objects can be found here:
--
-- http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1330536&SiteID=1
--
-- ------------------------------------------------------------------

declare @begDt datetime

declare @list table
( rid integer primary key,
delimited varchar(79),
blocked varchar(75)
)

-- ---------------------------------------------------------------------------
-- Generate the sample data. Note the use of the RIGHT built-in function to
-- right-justify the BLOCKED data.
--
-- We are setting up a list of 5 different floating numbers for each row.
-- There are 32767 total rows in the SMALL_ITERATOR table so we end up with
-- 32767 rows.
-- ---------------------------------------------------------------------------
insert into @list
select iter,

convert(varchar(15), dbo.rand()) + ',' +
convert(varchar(15), dbo.rand()) + ',' +
convert(varchar(15), dbo.rand()) + ',' +
convert(varchar(15), dbo.rand()) + ',' +
convert(varchar(15), dbo.rand()),

right(replicate (' ', 14) + convert(varchar(15), dbo.rand()), 15) +
right(replicate (' ', 14) + convert(varchar(15), dbo.rand()), 15) +
right(replicate (' ', 14) + convert(varchar(15), dbo.rand()), 15) +
right(replicate (' ', 14) + convert(varchar(15), dbo.rand()), 15) +
right(replicate (' ', 14) + convert(varchar(15), dbo.rand()), 15)
from small_iterator (nolock)

set @begDt = getdate()

declare @receiver table
( rid integer,
seq tinyint,
seg varchar(15)
)

-- ---------------------------------------------------------------------------
-- Processing of a DELIMITED list:
--
-- I chose CROSS APPLY so that the SPLIT function would be called for each
-- individual record. This is where you end up being heavily penalized for
-- the UDF.
--
-- On my desktop test server the processing of the 32K+ rows takes a little
-- under 23 seconds.
-- ---------------------------------------------------------------------------
/*
insert into @receiver
select rid,
OccurenceId,
splitValue
from @list
cross apply split (delimited, ',') b
*/
-- ------- 22610, 22720, 22796 ==> 22809


-- ---------------------------------------------------------------------------
-- Processing of a BLOCKED list:
--
-- Note that since each row contains a list of 5 different numbers that
-- we join to the SMALL_ITERATOR to be able to iterate through the list
-- of 5. Therefore, there is a little overhead in the join to the
-- iterator table; however, this is a much smaller overhead than the
-- overhead of calling a UDF on a line-by-line basis.
--
-- On my desktop test server the processing of the 32K+ rows takes little
-- under a second and a half -- WAY faster.
-- ---------------------------------------------------------------------------
insert into @receiver
select rid,
iter,
rtrim(substring (blocked, 15*iter-14, 15))
from @list
inner join small_iterator(nolock)
on iter <= 5

-- ------- 1343, 1346, 1390 ==> 1360

print ' '
select datediff (ms, @begDt, getdate()) as [Elapsed Time1]

select count(*) as RECORDS from @receiver





Re: Table valued function

Kent Waldrop 2007 Mar

I can post the comparison in which data is sprayed in a column fashion instead of row fashion if you want. But this was already long-winded enough.

:-) Kent





Re: Table valued function

Mahima

Hi Kent,

I am following the below approach.

select c.caCaseID,p.paSpeciesID,p.paGenderID

From paPatient p

Left join caCase c ON p.caCaseID=c.caCaseID

Inner join dbo.getCSV(@Species) Species on isnull(p.paSpeciesID,-1) = Species.[Col1]

inner join dbo.getCSV(@Gender) Genders on isnull(p.paGenderID,-1) = Genders.[Col1]

Thanks





Re: Table valued function

Kent Waldrop 2007 Mar

The ISNULL might not be a good idea; this might cause you to perform scans instead of seeks.



Re: Table valued function

Mahima

Hi Kent,

In my report I f the user is selecting the Null value for the species then I am passing the -1 to the Stored procedure thatswhy I have included the IsNULL( ..) in the condition.If we pass the Null value instead of -1 from the report to the SP how we get Species having Null cases.The null is coming from the report like this: @Species='1,2,Null' or @Species='Null'

How do we handle this situations withput using Isnull(p.paSpeciesID,-1) in (@species)

Any better idea

Thanks in advance.





Re: Table valued function

Kent Waldrop 2007 Mar

After thinking about it overnight I realized that it probably doesn't matter. This is probably not going to filter anything so this probably doesn't matter. If that is the case, go ahead and use the ISNULL.