473,840 Members | 1,607 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

optimizing SP with dynamic WHERE and ORDER BY

admittedly, this SP is probably a mess given that I am not a TSQL pro.
its purpose is to, based on the arguments, do 1) paging or 2) return
prev/next ids for a given record.

it does the job, but at the cost of several seconds. i was hoping
someone could give me pointers on how to optimize it. perhaps, i
should break it down into smaller SPs, or create additional indices on
the source table, etc.

thank you in advance for your help!

--sasha

-------------------start SP------------------------
CREATE PROCEDURE spProjects

@action int,
@currID int,
@sortBy varchar(50),
@sortDir varchar(4),
@Page int,
@RecsPerPage int,
@searchProjectN umber int,
@searchTitle varchar(255),
@searchPI int,
@searchOrg int,
@searchSponsor int,
@searchCreatedS tart smalldatetime,
@searchCreatedE nd smalldatetime

AS

SET NOCOUNT ON

DECLARE @nextID int
DECLARE @prevID int
DECLARE @currRow int
DECLARE @rowCount int
DECLARE @firstRec int
DECLARE @lastRec int
DECLARE @total int
DECLARE @more int
DECLARE @sortByFull varchar(255)

SELECT @sortByFull = @sortBy + ' ' + @sortDir
If @searchCreatedS tart IS NULL SELECT @searchCreatedS tart =
MIN(created_dat e) FROM spm_projects WHERE created_date IS NOT NULL
If @searchCreatedE nd IS NULL SELECT @searchCreatedE nd =
MAX(created_dat e) FROM spm_projects WHERE created_date IS NOT NULL

SELECT row = identity(int,1, 1), *
INTO #project_temp_t able
FROM ( SELECT TOP 100 PERCENT
r.id,
r.project_numbe r,
r.start_date,
r.end_date,
r.title,
r.created_date,
r.updated_date,
a.name agreement_type,
pu.name purpose_type,
sp.name sponsor,
pr.name prime,
p.lname pi_lname, p.fname pi_fname, p.mname pi_mi, p.email
pi_email,
o.name org,
convert(varchar (10), r.created_date, 101) created_date_c,
convert(varchar (10), r.updated_date, 101) updated_date_c
FROM spm_projects r, spm_agreement_t ypes a, spm_purpose_typ es pu,
spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE r.deleted <> 1
AND r.agreement_typ e_id = a.id
AND r.purpose_type_ id = pu.id
AND r.sponsor_id = sp.id
AND r.prime_id *= pr.id
AND r.pi_id = p.id
AND r.org_id = o.id
AND r.project_numbe r = ISNULL(@searchP rojectNumber,
r.project_numbe r)
AND r.title LIKE CASE WHEN @searchTitle IS NULL THEN r.title ELSE
'%' + @searchTitle + '%' END
AND p.id = ISNULL(@searchP I, p.id)
AND o.id = ISNULL(@searchO rg, o.id)
AND sp.id = ISNULL(@searchS ponsor, sp.id)
AND (r.created_date BETWEEN @searchCreatedS tart AND
@searchCreatedE nd OR r.created_date IS NULL)
ORDER BY
CASE WHEN @sortByFull = 'project_number asc' THEN r.project_numbe r
ELSE NULL END ASC,
CASE WHEN @sortByFull = 'project_number desc' THEN r.project_numbe r
ELSE NULL END DESC,
CASE WHEN @sortByFull = 'agreement_type asc' THEN a.seq ELSE NULL
END ASC,
CASE WHEN @sortByFull = 'agreement_type desc' THEN a.seq ELSE NULL
END DESC,
CASE WHEN @sortByFull = 'sponsor asc' THEN sp.name ELSE NULL END
ASC,
CASE WHEN @sortByFull = 'sponsor desc' THEN sp.name ELSE NULL END
DESC,
CASE WHEN @sortByFull = 'pi asc' THEN p.lname ELSE NULL END ASC,
CASE WHEN @sortByFull = 'pi desc' THEN p.lname ELSE NULL END DESC,
CASE WHEN @sortByFull = 'org asc' THEN o.id ELSE NULL END ASC,
CASE WHEN @sortByFull = 'org desc' THEN o.id ELSE NULL END DESC,
CASE WHEN @sortByFull = 'created_date asc' THEN r.created_date ELSE
NULL END ASC,
CASE WHEN @sortByFull = 'created_date desc' THEN r.created_date
ELSE NULL END DESC,
CASE WHEN @sortByFull = 'updated_date asc' THEN r.updated_date ELSE
NULL END ASC,
CASE WHEN @sortByFull = 'updated_date desc' THEN r.updated_date
ELSE NULL END DESC,
CASE WHEN @sortByFull NOT LIKE '%project_numbe r%' THEN
r.project_numbe r ELSE NULL END ASC
) x
-- prev/next
SELECT @currRow = row FROM #project_temp_t able WHERE id = @currID
SELECT @prevID = id FROM #project_temp_t able WHERE row = @currRow -
1
SELECT @nextID = id FROM #project_temp_t able WHERE row = @currRow +
1

-- paging
SELECT @firstRec = (@Page - 1) * @RecsPerPage
SELECT @lastRec = (@Page * @RecsPerPage + 1)
SELECT @more = COUNT(*) FROM #project_temp_t able WHERE row >=
@LastRec
SELECT @total = COUNT(*) FROM #project_temp_t able
SET NOCOUNT OFF

--SELECT @sortBy

-- prev/next
IF @action = 1 SELECT @prevID as prevID, @nextID as nextID

--paging
IF @action = 2
SELECT *, @more as more, @total as total
FROM #project_temp_t able
WHERE row > @firstRec AND row < @lastRec

DROP TABLE #project_temp_t able
Jul 20 '05 #1
0 3265

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
2285
by: JustSomeGuy | last post by:
I have a routine that evaluates a polynomial equation that have 3 variables x,y,z of orders 1,2,3 the coefficients of the polynomial are in an array. This routine is quite slow and I'd like to optimize it. Any suggestions? simply put pts is a vector of typdef struct double x; double y; double z;
32
5484
by: Bob | last post by:
Can anyone point me to a place where I can obtain the Microsoft C/C++ Optimizing Compiler Version 7.00. It was distributed about 10 years ago on floppy diskettes. I had a valid license but accidentally threw out the diskettes when I cleaned up once. Now I need to install it and I therefore am looking to replace the diskettes. Thanks.
8
1954
by: Hagen | last post by:
Hi, I have a question that you probably shouldn´t worry about since the compiler cares for it, but anyways: When you run your compiler with optimization turned on (eg. g++ with -Ox flag) and your program gets significantly faster than without, did you write bad code/ have a bad design? Cause what happens in those optimization steps is, I think, mostly
1
17688
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
7
1896
by: Abraham Luna | last post by:
how do i stop the dynamic validators from breaking explorer if i use a dynamic validator and move to a different control it breaks explorer and i can type in the page when i'm not supposed to. thank you.
7
3395
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always either AND or OR but never mixed together. We can use Northwind database for my question, it is very similar to the structure of the problem on the database I am working on. IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL DROP TABLE REPORT_SELECTION
2
2548
by: Jack | last post by:
I have a chunk of code that loads a few dozen function pointers into global variables. I'm concerned with unused memory consumption. What if the client only needs to use one or two functions? Then there's quite a few function pointers consuming memory and going to waste. Here's little example: // mycode.cpp or mycode.c typedef int (*PFN) (); PFN g_pfn;
4
3499
by: Got2Go | last post by:
Hello Group, I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I have a webpage that executes 9 queries one after the other, and then displays the results on the webpage. When the database was empty, this process was very quick. But, as the DB grew, it became slower.
24
3175
by: Richard G. Riley | last post by:
Without resorting to asm chunks I'm working on a few small routines which manipulate bitmasks. I'm looking for any guidance on writing C in a manner which tilts the compilers hand in, if possible, a compiler/underlying processor independant way : althought to be fair I cant see this stuff on anything other than x86, but who knows. I found some ok info here: http://www.eventhelix.com/RealtimeMantra/Basics/OptimizingCAndCPPCode.htm...
0
9860
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10922
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10660
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7838
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7023
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5685
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5874
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4076
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.