Dynamic Search SP

Hi,

I'm new to SQL 2005.

I need to create sp that perform search on Users table.

It gets few Parameters.

@.UserName

@.UserStatus

@.UserRole

@.OrderByColumn

All Parameters are optional, and i need to build sql statement that include only the parameters that the sp got on the specific.

I cand do that by concatanating a string and execute it using the sp_executesql().

Is it the best way in sql 2005 ?

Can you please show me an example using Case When or what ever ?

Thanks A lot.

Tok

A very good article on dynamic search that contains more than examples at:

http://www.sommarskog.se/dyn-search.html.

Please check it out.

Dynamic ORDER BY within stored procedure

I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.

CREATE PROCEDURE procedure_name
@.regNum varchar(14),
@.sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @.regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @.regNum
ORDER BY Obs_Date DESC
GO

Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:

ORDER BY CASE WHEN @.sortOrder = 1 THEN Obs_Date END DESC

but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.try something like :

EXEC ( ' SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',Obs_Date As ''Observation Date''
FROM tblSPG_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn +
' UNION
SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',
Obs_Date As ''Observation Date'' FROM tblRCH_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn )

hth

Dynamic lookup with periodical (Yearly) data using JOIN

Hi,

I am generating a dynamic SQL query to perform a lookup based on criterias entered by the user. The quey is generated by a C# application and the target database is Sql Server 2005.

Basically I have 2 tables:

--Cie-- (Over 40k companies, 1 rows per companies, total 40k rows)
(
CieId int not null,
Symbol varchar,
CieName varchar
) Primary key(CieId)

--YearlyData-- (Over 40k companies, 20 years of data per companies, total 800k rows)
(
CieId int not null,
Year int not null,
Revenue currency,
NetIncome currency,
[...over 170 columns]
)Primary key(CieId, Year)

//Sample Case:
The user want to get the list of all the compagnies (CieId) where
YearlyData.NetIncome > 25k for YearlyData.Year 2005
and YearlyData.Revenue > 100k for YearlyData.Year 2005
and YearlyData.Revenue > 95k for YearlyData.Year 2004
and YearlyData.Revenue > 90k for YearlyData.Year 2003
and YearlyData.Revenue of YearlyData.Year 2005 > YearlyData.Revenue of YearlyData.Year 2004

Currently, the Sql query that would be generated looks like this:

SELECT Cie.CieId
FROM Cie
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2005) Y2005 ON Y2005.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2004) Y2004 ON Y2004.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2003) Y2003 ON Y2003.CieId = Cie.CieId
WHERE 1=1
AND Y2005.NetIncome > 25000
AND Y2005.Revenue > 100000
AND Y2004.Revenue > 95000
AND Y2003.Revenue > 90000
AND Y2005.Revenue > Y2004.Revenue

I dont feel good about all those JOINs especially since there is up to 20 years of data and the user could use all of them in his query. The request become slower with each new join.

Does anyone see a better way to do this, maybe a PIVOT or something?

Thanks

Math:

I mocked this up establishing 40k cie entries and 800k yearly records. When I ran your query I got the expected execution plan and the expected performance. When the data is not cached it ran in about 15 seconds and with the data cached in ran in zero seconds. I got, of course, 3 table (clustered index) scans as it read in the yearly data. I got the merge join (which I expected).

My view is that (1) this is a report and (2) it doesn't figure to be run often and therefore performance for this is not critical. Now, I did show about 10,000 logical IOs as it scanned the tables. This will roughly amount to a grab of about 80Meg of buffer cache whenever this query runs. For most modern servers used for SQL Server 2005 this should not be a problem. Since (1) this is a report and doesn't figure to run often and (2) it seems to provide very adequate performance for a report I would not take any time to try to upgrade the performance of this; I don't figure you will get a good return on time invested in this report unless you are having some specific problems.

One thing that can be done to provide a marginal performance boost for this particular is to build a "cover index" for the specific columns you are using for this report; however, I don't feel like this is a good investment either. The modest boost received from the cover index does not figure to be a good tradeoff for the cost of the necessary cover index.

Are you having some specific problems? If so, what are they?

Dave

|||

Dave, thank you for the reply.

I dont have any specific problems other than perfomances, I am just trying to see if there is a better way to construct this kind of query (i.e. without 1 join per year). This is not really a report, it is more like a screener, a tools used when looking for companies to invest in. The screener is used frequently so performance is an issue. The data is readonly with only 1 update at the end of each day so we indexed every columns (grouped logicaly).

The query in the OP was only a sample case, the user can really compare:
"columnX of yearX" with "columnX of yearY"
"columnX of yearX" with "columnY of yearY"
"columnX of yearX" with "columnY of yearX"
"columnX of yearX" with a static value (like 250.95)
and more

Do you think the query can be resructured so it perform better (like removing the "join per year")?

Thanks

|||Would partitioning by year be helpful? It seems to be a common filter.|||

You can do below query instead and there is no reason to join with Cie table if you want just CieId values and there is FK relationship between YearlyData & Cie on CieId column.

select y.CieId

from YearlyData as y

where y.Year in (2003, 2004, 2005)

group by y.CieId

having max(case y.Year when 2005 then NetIncome end) > 25000

and max(case y.Year when 2005 then Revenue end) > 100000

and max(case y.Year when 2004 then Reveue end) > 95000

and max(case y.Year when 2003 then Reveue end) > 90000

and max(case y.Year when 2005 then Revenue end) > max(case y.Year when 2004 then Reveue end)

This query will provide best performance if there is an index on Year or (Year, CieId). You seem to have primary key on (CieId, Year) which will not help for this particular query resulting in a table/index scan.

|||

Math:

Both ideas help. When I ran Umachandar's query it reduced the logical IOs from about 10000 to about 3200, reduced alpha read time to about 4600 ms and reduced beta read time to around 420 ms. When I then partitioned the data on year logical IO was further reduced from 3200 to about 500, alpha read time from 4600ms to about 3200ms and beta read time from 420ms to about 380 ms.

Dave

|||

Both idea looks good, I will see if this query structure is compatible with all my test case and keep you inform.

Thank you for your help.

Dynamic Criteria for a Stored Procedure

Our database utilizes some custom functions and .dll's to perform IRR
calcuations. The IRR calculation takes two steps.
1. The first step is to create a view which preps the cash flows and
pulls out what you want to analyze. ("Ad Hoc Query")
2. The second step is to create a stored procedure that references the
Ad Hoc Query and performs the IRR calculation ("IRR sp"). The IRR sp
has three important parameters:
a. a start date. If the start date is null, then spGetPerformance
performs the calculation from the earliest date.
b. an end date. This is typically set to the most recent quarter
end date.
c. @.ShowTransactions. This allows us to audit the constituents of
the calculation. 0 = return a single record that is the IRR. 1 =
return all the constituents such that a user could transfer the
recordset to excel and perform the IRR calculation to double check that
spGetPerformance is performing well and there are no errors in data
entry for the constituents.
A sample of the IRR sp is written below:
ALTER PROCEDURE sp10_IRRSinceInceptionByInvestor
AS
BEGIN SET NOCOUNT ON
exec spGetPerformance @.AdHocQueryName =
'sp10_view_IRRSinceInceptionByInvestor',
@.StartDate = NULL, @.EndDate =
'6/30/2005', @.ShowTransactions = 0
END
---
Now I have about 30 of these IRR sp and they all have EndDates that are
the same or somehow systematically related (e.g. 5 are set one year
earlier, 5 are set 3 years earlier, etc.).
Is there a way to write a script, stored procedure, something else to
change the @.EndDate criteria for each IRR sp in a programatic way?
Now, I need to open each IRR sp and manually change the @.EndDate
parameter. I do have a table of IRR sp names and dates, but I am a
complete SQL novice, so I can't even so where to go next. I tried to
put dlookups in the criteria section but that doesn't seem to work.
Ryan(Ryan.Chowdhury@.gmail.com) writes:
> Now I have about 30 of these IRR sp and they all have EndDates that are
> the same or somehow systematically related (e.g. 5 are set one year
> earlier, 5 are set 3 years earlier, etc.).
> Is there a way to write a script, stored procedure, something else to
> change the @.EndDate criteria for each IRR sp in a programatic way?
> Now, I need to open each IRR sp and manually change the @.EndDate
> parameter. I do have a table of IRR sp names and dates, but I am a
> complete SQL novice, so I can't even so where to go next. I tried to
> put dlookups in the criteria section but that doesn't seem to work.
Nothing is impossible, and of course you could write a program to
do this. But for 30 procedures, I seriously doubt that it's worth the
effort. Had you said 300, it had been a different issue.
I will have to admit that I did not fully understand the setup, but if
you need to change the dates, maybe the scheme is flawed in some way.
Rather than hard-coding the values, maybe EndDate should be a parameter?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, I was told to run the IRR sp as an exec statement. I don't know
how to incorporate the EndDate as a parameter, although is it not a
parameter in the sp code above? It is just hard coded as 6/30/2005
right now.|||(Ryan.Chowdhury@.gmail.com) writes:
> Erland, I was told to run the IRR sp as an exec statement. I don't know
> how to incorporate the EndDate as a parameter, although is it not a
> parameter in the sp code above? It is just hard coded as 6/30/2005
> right now.
If I understand your description correctly, these procedures are somehow
generated. The scheme appeared strange to me, and may not be the best one.
But since I only know a fraction of what is going on in your organisation,
I can't say exactly how this should be changed.
If you think that having these 30 procedures that you need to change is
causing manageability problems, you should bring it up internally and
propose that a better solution to be developed.
As for the question you actually asked: yes, you could automate that change,
but, no, it would not be worth the effort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx