Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RobWafle
Starting Member
38 Posts |
 Posted - 2002-02-08 : 23:37:40
|
| Alright,I just had an idea how I can avoid some dynamic SQL statements in my database programming. I want to take advantage of SQL server's ability to create query plans, and I don't want it to execute one each time I run these querys.I have a query that has many possible options to the "where" clause. Sometimes values are not used and cannot be included in the code. Sometimes they are used, and need to be compared against.e.g. min max (if a user specifies a min value i want everything ABOVE the MIN ... MIN <= column)(if a user specifies BOTH min value and MAX value i want... and MIN <= column AND column <=MAX )(if a user specifies a min value i want everything BELOW THE MAX ... column <=MAX )also...the where clause must read:where CIK = ''ORwhere CUSIP = ''and..other columns i want to filter by are:statesicAND...I sometimes SORT my data (go figure)so, I have 2*3*2*2 permutations of the same stored procedure... Now.. I don't want to manage 24 different stored procedures. What if I need to add a column of data to my result set? That means i have to change the result 24 different times..Should I write code that creates all the possible permutations of this stored procedure? Then I can just manage one code base, and then run a script that will DROP then CREATE all the needed procedures.Then I can just call the appropriate stored procedure based on what sort I need to perform, or what where clause I need?Has anyone EVER heard of a teqnique like this? Am I outta my mind? I kinda think it will work... What % performance gain do you think I might see if I did this? (how much cost is associated with an ad-hoc SQL statement v.s. a precompiled stored procedure) |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 01:12:51
|
| Hi Mike,why dont you try using Case staments in your query and play with your conditions. it should greatly help you in putting if not all most of the conditions in one query . you can also use IF to furthur simplify your control flow.About Ad-hoc Sql Statments .BOL saysQuery plans produced from an ad hoc Transact-SQL query, including auto-parameterized queries. SQL Server caches the plans for ad hoc SQL statements for later reuse if the identical Transact-SQL statement is later executed.the cost vis a vis a PreCompiled Stored Procedure and a Ad-Hoc Sql Statement is. IMHO is everytime a query gets executed there should be a similar plan existing otherwise it will be created anew. on the otherhand a precompiled stored procedure execution plan resides in Chache after its first execution(ofcourse its not not recreated or called with Recompile option).HTH--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
RobWafle
Starting Member
38 Posts |
Posted - 2002-02-09 : 18:42:09
|
| nazim,I'm not trying to be lazy.. but my queries are somewhat complex.. I'd have to wrap case and if statements around 32 different versions of this code.. ? It just seems like a lot of work! What if I have to add a column? Then I have to modify 32 pieces of code to be exactly the same? .. I'm just a little confused. I want to reap the benefits of stored procedures and pre-compiled execution plans! I guess that maybe I should use dynamic for now, until I've played with these queries a little longer. select b.*, b .currentShares - b.lastShares as deltaShares, b.currentValue- b.lastValue as deltaValue, case b.lastShares when null then 0 else (((b.currentShares - b.lastShares) / cast (b.lastShares as float)) * 100) end as pctChgShares, case b.lastValue when 0 then null else (((b.currentValue - b.lastValue) / cast (b.lastValue as float)) * 100) end as pctChgValue into #tmp from ( select Dissem_Conformed_Name,dissem_CIK,address_state,address_city, isNull (CASE dissem_period_fixed WHEN "Q3" THEN positions_shares END, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_shares END) as fixedShares, CASE dissem_period_fixed WHEN "Q3" THEN positions_shares END as currentShares, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_shares END as lastShares, isNull (CASE dissem_period_fixed WHEN "Q3" THEN positions_valuex1000 END, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_valuex1000 END) as fixedValue, CASE dissem_period_fixed WHEN "Q3" THEN positions_valuex1000 END as currentValue, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_valuex1000 END as lastValue, 5 as P1, 6 as P2 from edgar..vwPositions where dissem_CIK = "0001078013" and positions_manager is not null and dissem_Year = 2001 or dissem_Year = 2001 and dissem_Quarter = 3 or dissem_Quarter = 2) as b;select (select count (*) from #tmp) as total_shares_held_holders, (select sum (currentshares) from #tmp) as total_shares_held_shares, (select count (*) from #tmp where currentshares > 0 ) as new_positions_holders, (select sum (currentshares) from #tmp where currentshares > 0 ) as new_positions_shares, (select count (*) from #tmp where currentshares > lastshares ) as increased_positions_holders, (select sum (currentshares) from #tmp where currentshares > lastshares) as increased_positions_shares, (select count (*) from #tmp where currentshares < lastshares ) as decreased_positions_holders, (select sum (currentshares) from #tmp where currentshares > lastshares) as decreased_positions_shares, (select count (*) from #tmp where currentshares = 0 and lastshares > 0 ) as soldout_positions_holders, (select sum (currentshares) from #tmp where currentshares = 0 and lastshares > 0 ) as soldout_positions_shares; select * from #tmp |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-09 : 23:11:07
|
| Rob,I've done similar types of designs. My solution is to mimic a data warehouse. A data warehouse is basically a "summary" table with the data stored in its most elemental unit: by date, by city, by code etc.. For example, let's say the elemental criteria is city and date.The user can specify multiple criteria. For example, the user wants to grab all the cities in a given region for a date range. I query a reference table and get all applicable cities and dates. I then JOIN it against my summary table. I then sum/agrregate to my heart's content.In short, 1 job to feed 1 summary table, 1 reference table, and 1 stored procedure. Hope that helps. |
|
|
|
|
|
|
|
|
Avoid Dynamic SQL?