How to multi-value param like SQL Reports in a query? | Tech Off

qwert231
M Kenyon

I trying to test some queries and I'd like to use a multi-value parameter like you can in SQL reports. You can have parameters that offer a selection, and some that use data from a sub-query to fill the parameter. How would I do that in a stored procedure?

Example:

SELECT Field1, Field2 FROM Table 1

WHERE Field1 IN (@param1)

AND Field2 IN (@param2)

Where @param1 is a selection of values and @param2 is a collection of values from the result of a sub query.

For @param1, I could manually put in {'value1', 'value2', 'value3'} or something like that. But I'd like to pass it into the stored procedure.

Is there a site online w/ a good example of this?

Maddus Mattus
Do, or do not. There is no try. - Yoda

I would use a table valued function, that string splits the parameter.

where field1 in (splitthis(@param1))

Sabot
My name is Dave Oliver. I'm a Technical Architect.

In reply to Maddus Mattus

#Sep 22nd @ 7:31 AM

Here you go ! A nice easy tutorial from Microsoft ... http://msdn.microsoft.com/en-us/library/ms345415.aspx

qwert231
M Kenyon

In reply to Sabot

#Sep 22nd @ 10:35 AM

Thank you.

The reason I'm doing this, is I'm trying to make the query more efficient. It times out when users try to run the report.

Select
count(*),
a.state,
case
  when a.state = '' then 'UNKNOWN - MISSING STATE'
  when a.state = s.state_code then s.description
  else 'UNKNOWN - OTHER' end as state_descr,
sum(td.state_amount) as net_amount,
(select
  sum(ttd.state_amount)
from
  ALSReplicator.tb_ora_tran tt
  JOIN ALSReplicator.TB_ORA_tran_detail ttd
   ON ttd.tran_ref_id = tt.tran_ref_id
  JOIN ALSReplicator.tb_ora_priv_def ppd
   ON ttd.priv_code = ppd.priv_code and ttd.license_year = ppd.license_year
where
  tt.process_date >= @begin_date and
  tt.process_date < dateadd(dd, 1, @end_date) and
  ttd.priv_code in (@priv_code) and
  tt.tran_status = 'C' and
  ttd.tran_status in ('I','C') and
  tt.test_tran = 'N' and
  ttd.replace_ind = 'N' and
  ttd.tran_detail_id not in
   (Select distinct purchase_tran_detail_id
   From ALSReplicator.tb_ora_voucher_detail) and
  ttd.tran_detail_id not in
   (Select distinct redeem_tran_detail_id
   From ALSReplicator.tb_ora_voucher_detail
   Where redeem_tran_detail_id is not NULL) and
  tt.location_id not in ('4811584', '4811287') and
  tt.location_id not in (@test_lid)
) as total_amount
From
ALSReplicator.TB_ORA_tran t
JOIN ALSReplicator.TB_ORA_tran_detail td
  ON t.tran_ref_id = td.tran_ref_id
JOIN ALSReplicator.TB_ORA_priv_def p
  ON td.priv_code = p.priv_code and td.license_year = p.license_year
JOIN ALSReplicator.TB_ORA_cust c
  ON t.cust_id = c.cust_id
LEFT JOIN ALSReplicator.TB_ORA_address a
  ON c.aid = a.aid
LEFT JOIN ALSReplicator.state s
  ON a.state = s.state_code
Where
t.process_date >= @begin_date and
t.process_date < dateadd(dd, 1, @end_date) and
td.priv_code in (@priv_code) and
t.tran_status = 'C' and
td.tran_status in ('I','C') and
t.test_tran = 'N' and
td.replace_ind = 'N' and
td.tran_detail_id not in
  (Select distinct purchase_tran_detail_id
  From ALSReplicator.tb_ora_voucher_detail) and
td.tran_detail_id not in
  (Select distinct redeem_tran_detail_id
  From ALSReplicator.tb_ora_voucher_detail
  Where redeem_tran_detail_id is not NULL) and
t.location_id not in ('4811584', '4811287') and
t.location_id not in (@test_lid)
Group By s.description, a.state, s.state_code

It's basically running the query twice. Once to get the sum total of one column. The second to get the individual resutls.

@test_lid and @priv_code are the multi value params. @test_lid comes from another query attached to the report, and the results are static. @priv_code is chosen by the user of the report. (Another query in the report populates the list and let's users choose the codes they want to search for.)

I've been adding a couple Indexes to try to speed things up, but what else can I do in this report to make it run faster.

Maddus Mattus
Do, or do not. There is no try. - Yoda

In reply to qwert231

#Sep 23rd @ 1:57 PM

You can also use common table expressions take make your query more understandable.

lensman

In reply to qwert231

#Sep 23rd @ 1:57 PM

What you are asking is a question of tuning. Tuning queries is more magic than logic. There is no 100% way to do things all the time.

In my experience you have the following problems:

You use the phrase "count(*)". This statement is evil. I prefer to see count('x'). While they both produce the same results, your version requires the database scan the entire datastore, including all columns, while mine does not. Try counting the number of rows in a 10 million row by 900 column table using count(*) and try it with count('x'). Mine is faster.
You seem to love subselects. Subselects are evil. Move them to the "from" clause and use the correct joins. As your usage seems to require a left outer join. By moving subselects to the main "from" clause the optimizer can more effectiently narrow down your data quicker.
Why are you using a select in your select statement? Technically it is legal however it is not effecient as the same logic in the "from" clause.
Move as much of your "where" block into the "from/ on" area. While both areas are funtionally the same when used in a ON block the optimizer is more effecient. Caution: LEFT / RIGHT outer joins cause problems. Data defining conditions which should not exist must be in the where clause.
While I do not have your data, make sure all your datatypes are correct. For example a date which is passed in as varchar must be converted, on the fly, to a date/time value when compared against a date/time value. By making sure both sides of comparisions are compatible types will speed your queries.

In general you should also consider that the most efficent queries narrow down the data required to produce the desired results quickly. If your query needs to find 10 records out of 10 million rows and perform actions against those rows, performing those actions against all 10 million rows and only showingn the results of the 10 is a waste of time.

The order you put tables in a FROM block can affect performance. Some will tell you that I am nuts however the analyzer shows different results based upon the order of tables. If you are trying to sqeek more performance out of your queries consider this as the last resort.

qwert231
M Kenyon

In reply to lensman

#Sep 24th @ 8:40 AM

Thank you for the tips.

I was already trying the count(columnName) instead of count(*) which gave some improvement, so thank you for that. That confirms one thought I had.

The sub query (for total_amount) will return 1 result, that the designer of this would like for every record of the main select. I can do a 'JOIN (Select... ) AS subTable', but I'm not sure what to do, if anything, for the ON statement.

lensman

In reply to qwert231

#Sep 24th @ 11:37 AM

Try something like the following:

From   ALSReplicator.TB_ORA_tran t JOIN
       ALSReplicator.TB_ORA_tran_detail td ON
           t.tran_ref_id = td.tran_ref_id JOIN
       ALSReplicator.TB_ORA_priv_def p ON
           td.priv_code = p.priv_code and
           td.license_year = p.license_year JOIN
       ALSReplicator.TB_ORA_cust c ON
           t.cust_id = c.cust_id LEFT JOIN
       ALSReplicator.TB_ORA_address a ON
           c.aid = a.aid LEFT JOIN
       ALSReplicator.state s ON a.state = s.state_code LEFT OUTER JOIN
       (
           Select distinct purchase_tran_detail_id AS F
           From ALSReplicator.tb_ora_voucher_detail
           UNION ALL
           Select distinct redeem_tran_detail_id AS F
           From ALSReplicator.tb_ora_voucher_detail
           Where redeem_tran_detail_id is not NULL
       ) AS I1 ON
           td.tran_detail_id = I1.F
Where   t.process_date >= @begin_date and
       t.process_date < dateadd(dd, 1, @end_date) and
       td.priv_code in (@priv_code) and
       t.tran_status = 'C' and
       td.tran_status in ('I','C') and
       t.test_tran = 'N' and
       td.replace_ind = 'N' and
       I1.F IS NULL AND
       t.location_id not in ('4811584', '4811287') and
       t.location_id not in (@test_lid)
Group By s.description, a.state, s.state_code

Your original logic has two "not in" clauses. They were effectively returning a value you did not want the field "td.tran_detail_id" to ever be. By using a left outer join you specify the condition where the fields would match. Assuming they did not match, the value of "I1.F" would be null. Thus by saying "I1.F IS NULL" you are in effect saying the same thing as "not in".