Without dynamic SQL, Top X Query
-
Atiq,
I don't understand your technique. Does "x" in the subquery mean a parameter? I tried running this on pubs, and as expected it failed.
declare @int int
select @int = 10
select top 10 *
from dbo.authors
Where au_id not in ( select top @int au_id from dbo.authors )
Signature is NULL
-
Calvin Lawson,
Here is the example:
select top 5 * from dbo.authors Where au_id not in (select top 0 au_id from dbo.authors order by au_id)
select top 5 * from dbo.authors Where au_id not in (select top 5 au_id from dbo.authors order by au_id)
Regards,
-
Atiq,
Thanks for expaining that. It wasn't clear to me, either.
You haven't found a way to make the '5' variable, have you?
-
Ok I am late to the scene, and not sure if my input will be useful to this user, or perhaps others. This is however something that I recently addressed and it works well, eliminating the performance hits that are mentioned in this thread. I will paste some of my SP here, and add comment lines in places to describe what I have done. If anyone has a way that performs better I am all ears as I would like to increase performance anyway I can. So far however this SP (which is just being finished up) seems to do well.
I work in the telecommunications industry and this SP (which is undergoing some change right now to accommodate XML input) is designed to return a list of available phone numbers from inventory, based upon several rules. One of the parameters is to return the top X rows matching the business rules. There is a default set and the query source can specify their own number. One of the problems that I ran into was that if the ToP X was specified as a higher number than the return set could result in then performance went down the drain big time; this too has been addressed; so on with the SP:
*******************************************************
CREATE PROCEDURE dbo.GET_AvailableDID
--DECLARE
-- Moving INPUT to XML Format
(
@xmlDocument varchar(8000)
,@MSGID varchar (10) OUTPUT
)
/*-- Starting Here comment out individual Parameters; moving to XML input
(
@PartnerID AS int,
@NPANXX AS varchar ( 6 ),
@rcabbrev AS varchar ( 10 ),
@state AS varchar ( 2 ),
@Top AS int = 10,
@EndWith AS varchar ( 4 ) = '%', -- @EndWithL Future Deployment
@EndWithL AS int = '', -- @EndWithL Future Deployment, not yet in use
@did AS varchar ( 25 ) = '',
@bunit1 AS varchar ( 10 ) = 'any',
@bunit2 AS varchar ( 10 ) = '',
@bunit3 AS varchar ( 10 ) = ''
)
*/
AS
--BEGIN -- DECLARE Environment Variables
DECLARE @TotalAvail AS int
,@MSGLen int
,@UniqueID1 as uniqueidentifier
,@UniqueID2 as uniqueidentifier
,@PartnerID AS int
,@SubReqID as int -- This value is passed in with the request. If multiple Rate Center / State combos are requested then this identified each sub part of the request
,@NPANXX AS varchar ( 6 )
,@rcabbrev AS varchar (20)
,@state AS varchar (2)
,@TopCount AS int
,@EndWith AS varchar (4)
,@EndWithL AS int
,@did AS varchar (25)
,@bunit1 AS varchar (10)
,@bunit2 AS varchar (10)
,@bunit3 AS varchar (10)
,@ReqSourceLogin as varchar(50) -- The ID of the person logged in at the request side, placing the order or request.
,@idoc int
,@Specified as bit
--END
--BEGIN -- SET Default Variables
SET @TotalAvail = 0
SET @Specified = 0
SET @MSGLen = 10
SET @UniqueID1 = (newid())
SET @UniqueID2 = (newid())
SET @MSGID = dbo.CreateMSGID (@MSGLen, @UniqueID1, @UniqueID2)
--END
/* - SET Debug Variable values
--BEGIN -- SET Debug Data Set
--SET @EndWithL = LEN(@EndWith)
-- Need to add logic that will limit result set to Allocation Units that the source has access to.
-- Need to add logic to limite output to include on Business Units that are valid for source
--Test Data
--SET @NPANXX = '201'
--SET @rcabbrev = 'UNION'
--SET @state = 'NJ'
--SET @BUnit1 = 'any'
--SET @did = ''--'212678'
--SET @TopCount = 500
--SET @PartnerID = 1234
--SET @EndWith = '347' -- Do not use, performance issues
-- XML Input example used in test execution
<CGetAvailDID PartnerID = "000000" ReqSourceLogin = "jwilliams">
<ReqDetail SubReqID = "1" NPANXX = "201" rcabbrev = "Union" State = "NJ" Top= "2" DID = "" bunit1 = "Any" bunit2 = "" bunit3 = ""/>
<ReqDetail SubReqID = "2" NPANXX = "216" rcabbrev = "Cleveand" State = "OH" Top= "5" DID = "" bunit1 = "Any" bunit2 = "" bunit3 = ""/>
</CGetAvail>
*/
DECLARE @DetailTable TABLE
( MSGID varchar (10)
,SubReqID int
,NPANXX varchar (6)
,rcabbrev varchar (20)
,state varchar (2)
,TopCount int
,TotalAvail int
,bunit1 varchar (10)
,bunit2 varchar (10)
,bunit3 varchar (10)
 
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDocument
BEGIN TRANSACTION
INSERT INTO DIDRequestHeader
(MSGID, PartnerID, ReqSourceLogin)
SELECT @MSGID , PartnerID, ReqSourceLogin
FROM OPENXML (@idoc, 'CGetAvailDID' ,1)
WITH (
PartnerID int '@PartnerID'
,ReqSourceLogin varchar(50) '@ReqSourceLogin'
)
COMMIT TRANSACTION
BEGIN -- SET Default Values for Variables Not passed in
IF @BUnit1 IS NULL
SET
@BUnit1 = 'Any'
IF @NPANXX IS NULL
SET
@NPANXX = ''
IF @RCAbbrev IS NULL
SET
@RCAbbrev = ''
IF @State IS NULL
SET
@state = ''
IF @EndWith IS NULL
SET
@EndWith = ''
IF @EndWIthL IS NULL
SET
@EndWithL = 10
SET
@EndWithL = LEN
(
@EndWith
)
IF
(
@did = ''
Or @DID IS NULL
)
BEGIN -- Check to see if a specific DID was passed in
if(@did = '' or @DID is null)
BEGIN
SELECT -- SELECT (Determine quantity available that matches query criteria)
-- NOTE FOR FORUM POST: This is where I am determining the MAX Count available for this query. This will be used to determine if the requested quantity exceeds the available quantity.
@TotalAvail = count ( * )
FROM
lerg_6_tek_orig AS l6
INNER JOIN
didinv AS inv
ON
LEFT
(inv.did,6) = l6.npanxx JOIN
L_DIDStatus s
ON s.statusid = inv.statusid
And s.Available = 1 JOIN
bunit AS bu
ON bu.bunitid = inv.bunit JOIN
Entity AS e
ON e.PartnerID = @PartnerID JOIN
EntityAllocation AS ea
ON ea.EntityID = e.EntityID
And ea.AllocationID = inv.AllocationID
WHERE
l6.NPANXX Like
(
@NPANXX + '%'
)
And rc_abbre Like
(
@rcabbrev + '%'
 
And loc_state Like
(
@state + '%'
 
And
(
bu.bunit = @bunit1
Or bu.bunit = @bunit2
Or bu.bunit = @bunit3
 
-- AND inv.DID LIKE ('%' + RIGHT (@EndWIth, @EndWIthL))
IF -- IF (Determine if Total Available matches is < the quantity requested
(
@TotalAvail < @TopCount
 
BEGIN -- IF Total Available less than requested quantity then set request to total available
SET
ROWCOUNT @TotalAvail
END
ELSE
BEGIN -- IF Total Available = or > requested amount then process requested quantity
SET
ROWCOUNT @TopCount
END
SELECT
DISTINCT npanxx,
did,
rc_abbre,
loc_state,
bu.bunit,
s.statusid,
s.status_descr AS StatusDesc,
s.available AS Available
FROM
lerg_6_tek_orig AS l6
-- WITH (FASTFIRSTROW , NOLOCK )
I hope this helps (IF you are still looking for a better solution, or likely if anyone else is looking for a way to handle this
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
With 7 million rows of data, I'm thinking that you'll get a recompile on run anyway just due to the number of changes in the data. So, I gotta ask, why the phobia about dynamic SQL here? It sounds like a read-only task so I'm thinking you won't have a problem exposing the DB to an injection attack in this instance. Dunno... maybe I'm wrong... just seems like a lot of work without much to gain.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply