May 16, 2009 at 7:42 pm
I have a complicated select statement joining about 10 tables in a stored procedure. I need to select by a variety of different criteria so I have the same select statement repeated over and over in several SPs with different where clauses.
Is there a better way to do this? I have thought about dynamic SQL which is probably not ideal, either.
Sam
May 17, 2009 at 4:37 am
Hello Sam,
May be creating a View based on your Select statement would save some coding?
If any parts of the various Where Clauses are common, then these could also be included in the View.
I personally avoid dynamic SQL wherever possible.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 7:15 am
I had tried views before but performance was slow. I guess my best option may be what I'm doing. Just seemed redundant.
May 17, 2009 at 7:34 am
smknox, you have 2 options here.
1. Proceed with multiple stored procedures. But you need to make the application to choose which one to call basing on what's selected in the drop-down box.
2. You may create "search" table containing ParameterID ( relates to "Name", "Number", "Date", "Contact Phone", other searchable column names in look-up table), ParameterValue and reference to tables' PK.
Then you're gonna need to populate this table every time the columns you want to search are updated. It may be done within triggers or/and scheduled job.
_____________
Code for TallyGenerator
May 17, 2009 at 8:18 am
Hello Sam,
Obviously I don’t know your application (even whether it is DW or OLTP?), but you mention that a complex Select is used in many places, I just wonder then if it would be worthwhile creating an Indexed View? That might solve the performance issues that you experienced.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 9:41 am
You could use a master stored procedure that executes the proper child stored procedure based on the parapmeters passed to the master stored procedure.
May 17, 2009 at 3:07 pm
It seems like my simplest option might be to pull lots of records over the network (one sp without a where clause) and then filter out what I want on the frontend.
Sam
May 17, 2009 at 3:27 pm
smknox (5/17/2009)
It seems like my simplest option might be to pull lots of records over the network (one sp without a where clause) and then filter out what I want on the frontend.Sam
Well, you can do it, of course.
If you don't have plans to work for the company anymore.
If you have some thoughts about it then populate your database with 5 years worth amount of data and try to pull it over network.
It will explain you why they would not want to see you on the job after couple of years.
_____________
Code for TallyGenerator
May 17, 2009 at 9:07 pm
smknox (5/17/2009)
It seems like my simplest option might be to pull lots of records over the network (one sp without a where clause) and then filter out what I want on the frontend.Sam
Again, I'd suggest a master stored procedure that calls the appropriate child stored procedure based on the parameters passed to the master stored procedure.
May 17, 2009 at 10:49 pm
Hello Sam,
Generally speaking it is preferable to filter records at the Server, particularly in order to reduce Network Traffic.
A slight alternative to Lynn Pettis’s suggestion would be to have a Master Table-Valued UDF that is called by the “Child SPs”. Table-Valued UDFs can be a bit more flexible as you can use them directly in code where you can’t use an SP.
If you do encounter performance problems, and the data in question is being heavily queried then bear in mind my previous suggestion of an Indexed View.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 18, 2009 at 8:35 am
John Marsh (5/17/2009)
Hello Sam,Generally speaking it is preferable to filter records at the Server, particularly in order to reduce Network Traffic.
A slight alternative to Lynn Pettis’s suggestion would be to have a Master Table-Valued UDF that is called by the “Child SPs”. Table-Valued UDFs can be a bit more flexible as you can use them directly in code where you can’t use an SP.
If you do encounter performance problems, and the data in question is being heavily queried then bear in mind my previous suggestion of an Indexed View.
Regards,
John Marsh
Be careful, remember that the Query Optimizer will consider that multistatement Table Valued Functions have only 1 row regardless of the actual number of rows which means you may not get an efficient query plan.
May 20, 2009 at 6:17 am
yes, I'd like to keep my job, so will work on the child SPs. That sounds like the best option. Thanks so much!!
Sam
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply