How I build my SQL strings

[disruptivehair]

This is the entire module I wrote for building SQL strings for my application. All of this was previously contained in a single sub so I broke it up into individual 'tasks', if that makes any sense. Since I'm still pretty new at this I don't know if that's good or bad practice.

Good, bad, inefficient, maintainable? Do your worst; I'm prepared to take it! There are several functions so if you have any questions about how they work or if I've left anything out please let me know.

TIA.

' This function fetches the subfilter condition from the Conditions table
' on SQL Server and returns it to the calling sub only if there is a selected
' value on the subfilter.  If there is no selected value or the selected
' value is 'All', the function returns nothing.  The sub-filter is the combo
' box on Criteria Selection where the user can choose values for Application
' Type, Critical Illness, Missing Items, or Sum Assured.
 
Public Function GetCondition(strSubFilterValue As String, strSQL As String) As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
' Call CheckConnection
 
Dim strCondition As String
 
' // If the LENGTH of the subfilter value is 0 then nothing is selected, strCondition
' is set to empty; if the value is 'ALL' then no filtering needs to be applied and
' strCondition is likewise set to empty.
If Len(strSubFilterValue) = 0 Then
    strCondition = ""
ElseIf strSubFilterValue = "All" Then
    strCondition = ""
    
' // If the LENGTH of the subfilter value is greater than zero this indicates the user
' has selected something, so a recordset is opened and the SQL source passed to the function
' is executed.
ElseIf Len(strSubFilterValue) > 0 Then
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    strCondition = rst.GetString
    rst.Close
    Set rst = Nothing
End If
 
GetCondition = strCondition
 
End Function

' This function uses the tab the user is on and the value of the filter they've
' selected to construct part of the 'WHERE' clause SQL Server will need to return
' the appropriate data.  Example 'filter' values are York, Bankhall, CONTROL, etc.
' This function does not actually include 'WHERE' or 'AND'; that part of the string
' is constructed by another function.
 
Public Function GetClause(strTabValue As String, strFilterValue As String) As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
' Call CheckConnection
 
    Dim strClause As String
 
    ' Builds a SQL clause based on what tab the user is currently using; if the tab value
    ' is "Headof" then no clause is built as no filter is actually being used.
    Select Case strTabValue
        Case Is = "HeadOf"
            strClause = ""
        Case Is = "Distributor"
            strClause = "Distributor = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Site"
            strClause = "Site = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Team"
            strClause = "Team = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Is = "Parent"
            strClause = "ParentCompany = " & "'" & "'" & strFilterValue & "'" & "'"
        Case Else
            strClause = ""
    End Select
    
    GetClause = strClause
 
End Function

' This function uses the values of strCondition and strClause plus
' what tab the user is on to determine how to build the parameter string.
' On the HeadOf tab, the only time there will be a WHERE clause is if
' a sub-filter value is selected; otherwise there is no WHERE clause.
'
' On other tabs if a sub-filter value is selected it must be preceded by AND,
' not WHERE, as you can only use the WHERE keyword once in a normal query
' and we'll already be using WHERE for Distributor, Site, Team, or ParentCompany.
 
Public Function GetParameter(strCondition As String, strTabValue As String _
    , strClause As String) As String
 
    ' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
    ' is closed then open it.
    'Call CheckConnection
    
    Dim strParameter As String
    
    Select Case strTabValue
        ' If the user is on HeadOf and no condition is selected then no WHERE clause is built
        ' If a condition (subfilter) is selected then a WHERE clause is constructed
        Case Is = "HeadOf"
            If strCondition = "" Then
                strParameter = ""
            Else:
                strParameter = " WHERE " & strCondition
            End If
        ' If the user is on any other tab and no condition (subfilter) is selected then the
        ' WHERE clause is constructed using only the selected distributor, site, etc.
        ' If a condition (subfilter) is selected then an AND clause is tacked onto the
        ' end of the WHERE clause
        Case Is = "Distributor", "Site", "Team", "Parent"
            If strCondition = "" Then
                strParameter = " WHERE " & strClause
            Else: If Len(strCondition) > 0 Then strParameter = " WHERE " & strClause & " AND " & strCondition
            End If
        ' This should never happen, but in case it does GetParameter returns a zero-length string
        Case Else
            strParameter = ""
    End Select
    
    GetParameter = strParameter
 
End Function

' Executes GetClause, GetCondition, and GetParameter to build an appropriate
' SQL string based on the user's selections on the CriteriaSelection form.
' This is the string that will be passed to the appropriate stored procedure
' as its parameter.
 
Public Function DeriveWhereClause() As String
 
' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
' is closed then open it.
 Call CheckConnection
 
Dim strParameter As String          ' Holds value of parameter
Dim strCondition As String          ' Holds value of condition
Dim strClause As String             ' Holds value of clause
Dim strTabValue As String           ' Holds what tab user is currently on
Dim strFilterValue As String        ' Holds value of filter, i.e. 'York', 'Bankhall', etc
Dim strSubFilterValue As String     ' Holds value of subfilter, i.e. 'paper', 'dirty', etc
Dim strAddInfoType As String        ' Holds value of subfilter type, i.e. 'Missing Items', 'Critical Illness', etc
Dim strSQL As String                ' Source SQL
 
' Call global variable functions and assign results to local variables
strTabValue = GetgVarActiveTab()
strFilterValue = GetgVarPrimInfoValue()
strSubFilterValue = GetgVarAddInfoValue()
strAddInfoType = GetgVarAddInfoType()
 
' Initialize strSQL
strSQL = "SELECT SQLResult FROM Conditions WHERE QueryField = " _
    & "'" & strAddInfoType & "'" & " AND Value = " & "'" & strSubFilterValue & "'"
 
' Call GetCondition function and assign result to strCondition variable
If Len(strSubFilterValue) > 0 Then
    strCondition = GetCondition(strSubFilterValue, strSQL)
Else
    strCondition = ""
End If
 
' Call GetClause function and assign result to strClause variable
If Len(strTabValue) > 0 Then
    strClause = GetClause(strTabValue, strFilterValue)
Else
    strClause = ""
End If
 
' Call GetParameter function and assign result to strParameter variable
strParameter = GetParameter(strCondition, strTabValue, strClause)
 
DeriveWhereClause = strParameter
 
End Function

Public Function MakeSQLString() As String
 
    ' Check to make sure connection still exists/is open.
    Call CheckConnection
 
    Dim strSQL As String
    Dim strSQLObject As String
    Dim strSource As String
    
    Dim rst As ADODB.Recordset
    
    strSource = "SELECT SQLObject FROM tblReports WHERE ReportName = " & _
        "'" & GetgVarReportSelected() & "'"
    
    Set rst = cnConn.Execute(strSource)
    strSQLObject = rst.GetString
    
    ' Status History and CELT Data Summary cannot be filtered in any
    ' way so their SQL strings are hard-coded here.  Weekly Summary
    ' is not handled by this function at all.
    Select Case GetgVarReportSelected()
        Case Is = "Status History"
            strSQL = "EXEC sp_StatusPositionSummary"
        Case Is = "CELT Data Summary"
            strSQL = "EXEC sp_FEWATCELT"
        Case Else
            strSQL = "EXEC " & strSQLObject & " @Filter = " _
                & "'" & DeriveWhereClause & "'"
    End Select
      
    MakeSQLString = strSQL
    
    rst.Close
    Set rst = Nothing
    
End Function

[szlamany]

Why are you using in-line SQL statements?

Have you considered doing everything with STORED PROCEDURES?

[disruptivehair]

Why are you using in-line SQL statements?

Have you considered doing everything with STORED PROCEDURES?

That's what we use. MakeSQLString() builds a where clause which is passed to the stored procedure as a parameter.

[szlamany]

So - it's dynamic SQL in the SPROC - right?

Looks at this thread - post #2 and post #16 are the main points I wanted to demonstrate.

http://www.vbforums.com/showthread.p...40idlist+table

It's an alternative to building dynamic WHERE clauses.

We use both techniques - probably #2 more often. But when speed becomes an issue we go with #16.

We have never yet used dynamic SQL in a SPROC (well over 1500 SPROCS in 6 years now!). Probably one of the most deciding reasons for that is we like to put all the business logic in the SPROC - we consider it our middle-tier (business layer). Our SPROCS self-bind at run-time to textboxes or flexgrid columns - based on matching the name of the parameter to the UI objects.

So adding a new "feature" to the app means only putting the textbox, for example, on the form. The SPROC self-binds and all our IF/WHERE business-logic sits in the SPROC. Effectively we add new BL requirements to the app without ever releasing a new .exe...

[disruptivehair]

So - it's dynamic SQL in the SPROC - right?

Looks at this thread - post #2 and post #16 are the main points I wanted to demonstrate.

http://www.vbforums.com/showthread.p...40idlist+table

It's an alternative to building dynamic WHERE clauses.

We use both techniques - probably #2 more often. But when speed becomes an issue we go with #16.

We have never yet used dynamic SQL in a SPROC (well over 1500 SPROCS in 6 years now!). Probably one of the most deciding reasons for that is we like to put all the business logic in the SPROC - we consider it our middle-tier (business layer). Our SPROCS self-bind at run-time to textboxes or flexgrid columns - based on matching the name of the parameter to the UI objects.

So adding a new "feature" to the app means only putting the textbox, for example, on the form. The SPROC self-binds and all our IF/WHERE business-logic sits in the SPROC. Effectively we add new BL requirements to the app without ever releasing a new .exe...

We can't use simple filters like that because the where clause could be absent, it could be followed by an AND clause, or it could not be. It could be 'WHERE Distributor = 'Blah'' or 'WHERE Site = 'India' AND EDI = 'Y'' or it could be ''. We just never know. Since it's so changeable we decided that passing the entire where clause as a parameter was the easiest way to go.

Here is a sample stored procedure that we use:

CREATE PROCEDURE dbo.sp_FEVBWeeklySummary_Res(@Filter nvarchar(200) = NULL)
AS

DECLARE @strSQL nvarchar(3000)

SET @strSQL = N'SELECT WeekStart
, SUM(CASE [Grouping] WHEN ''Term'' THEN Cases ELSE NULL END) AS ''Term''
, SUM(CASE [Grouping] WHEN ''MLI'' THEN Cases ELSE NULL END) AS ''MLI''
, SUM(CASE [Grouping] WHEN ''PTA'' THEN Cases ELSE NULL END) AS ''PTA''
, SUM(CASE [Grouping] WHEN ''Other Protection'' THEN Cases ELSE NULL END) AS ''Other Protection''
, SUM(CASE [Grouping] WHEN ''NTU'' THEN Cases ELSE NULL END) AS ''NTU''
, SUM(CASE [Grouping] WHEN ''Decline-Defer'' THEN Cases ELSE NULL END) AS ''Decline-Defer''
FROM vwFEWeeklySummary_Res'
+ @Filter + 
' GROUP BY WeekStart'
	

EXEC sp_executesql @strSQL


GO

[szlamany]

I appreciate the approach you are taking - it seems robust.

Since we live with a 0%-BL-requirement in the vb client we need to come up with ways to do that same in the SPROC itself.

Our SPROCS end up with lots of IF/BEGIN/END blocks - lots of different paths to follow based on the parameters.

It's not a lot different then what you are doing in the long run.

You could probably do all the VB WHERE clause building in the T-SQL SPROC, and still use the dynamic execute...

[disruptivehair]

I appreciate the approach you are taking - it seems robust.

Since we live with a 0%-BL in the vb client we need to come up with ways to do that same in the SPROC itself.

Our SPROCS end up with lots of IF/BEGIN/END blocks - lots of different paths to follow based on the parameters.

It's not a lot different then what you are doing in the long run.

You could probably do all the VB WHERE clause building in the T-SQL SPROC, and still use the dynamic execute...

We probably could, but when we did this we were running Access 97 as a front end on SQL and this seemed the better option. Plus we think this is a much more simple option than having to code all of it in VB.

I appreciate that you think it's robust; our DBAs hate it and are threatening to disallow it after 8 months of rubber-stamping my code.

[szlamany]

Is the DBA argument against the use of dynamic SQL?

[disruptivehair]

Is the DBA argument against the use of dynamic SQL?

Nope, we already went round and round on that when I tried to use EXEC(), which they let slip past for several months before they griped about it. I could see their point there though I thought their fears about injection attacks were slightly silly since we're not using web apps and our users are so thick I had to add a special Print button...I doubt injection attacks were ever really a risk but hey, it's their DB server...so I changed my stored procedures without complaining.

Their objection here is that I'm using stored procedures at all; they think I should build all my SQL in the VB app which I think is a stupid idea since many of the dynamic queries are complex. They got this idea because the sproc I sent to them to promote to production is concatenating a simple select statement with a where clause. Now they're being snarky about my table's indexes, but one of them did an analysis and said there wasn't any point in adding more indexes. There are six of them and you can ask them all the same question and get six wildly different answers. This has happened before and it will happen again. They're fearful of being outsourced and at this moment I can't really say that I think it's a bad idea. I think as individuals they're good guys, but as a team they STINK.

[RhinoBull]

We can't use simple filters like that because the where clause could be absent, it could be followed by an AND clause, or it could not be...

That sin't a problem though... One trick you can use is to permanently add to your select statement the following where clause:

Select...
From...
Where 1=1

All you have to do now is add as many additional ANDs as you can possibly need.
You can build all of your filters in the fron end and pass it to SP and then append it at the end of sql (after 1=1).

I actually agree with your DBA - multiple if-else make SP run slower. Just instead of "disallowing" he should help you to tune it up.

[disruptivehair]

That sin't a problem though... One trick you can use is to permanently add to your select statement the following where clause:

Select...
From...
Where 1=1

All you have to do now is add as many additional ANDs as you can possibly need.
You can build all of your filters in the fron end and pass it to SP and then append it at the end of sql (after 1=1).

I actually agree with your DBA - multiple if-else make SP run slower. Just instead of "disallowing" he should help you to tune it up.

Thanks RB, I'll try that.

And I agree...they SHOULD help. But they don't...they think it is not their job to help anyone.

[disruptivehair]

I should add that I don't feel the problem is on my end; the DBAs not only do not like the fact that we're using stored procedures but they think the performance of my queries is suffering. They keep telling me that my queries are doing 'complete table scans' but when I display the execution plan in SQL Server, I NEVER EVER EVER see a table scan of any kind. The only bits I see are:

Select
Compute Scalar
Hash Match/Left Outer Join
Computer Scalar
Nested Loops/Inner Join
Clustered Index Scan x 3
Table Spool/Lazy Spool

This is what *I* saw on a stored procedure where they CLAIM they saw three table scans. I've seen table scans show up on execution plans before and they say 'TABLE SCAN' right underneath them. I asked the DBAs if perhaps they're referring to something else and of course they never responded. Now, these names and icons don't mean very much to me; they are not covered in any SQL book I own nor do the books online tell me anything about them. I don't know what a 'table spool/lazy spool' is and I don't know what in my SQL is triggering it. I have no idea. The DBAs won't tell me. All they do is claim they're seeing table scans when I don't see table scans and have not since we indexed our tables. If I create a table with dummy data and no indexes then query it I can see the 'table scan' icon in the execution plan. I NEVER see this icon when running execution plans for my application's stored procedures, so why are THEY telling me that they're seeing it?

What are they getting at? I'm tired of shooting for a moving target. We have researched our DB design and have indexed the most frequently accessed columns. We re-examine our DB's indexes and performance periodically and we asked the DBAs to run an index analysis after which they told us our DB was FINE. Now they're essentially claiming that we have NO indexes in our main data table when in fact we have several. Who here is full of bunk...us, them, or all of us? We're busting our butts trying to make our DB as efficient and fast as possible and they keep changing the rules on us and feeding us contradictory information. How can we work together when they don't even believe us when we tell them we're not seeing table scans?

[szlamany]

Can you post the picture image from the execution plan - so I can see rowcounts and percentanges associated with steps...

[disruptivehair]

Can you post the picture image from the execution plan - so I can see rowcounts and percentanges associated with steps...

You can't see the rowcounts here unless I hover over the icons, but you can see the percentages.

http://i16.photobucket.com/albums/b2...cutionplan.jpg

[szlamany]

Where CITYPE is NOT NULL

Is CITYPE an INDEX? And it allows NULL's? I'm not so sure how NULL's are handled in INDEX SCANS - I'm not sure I have ever indexed a column that allows null's...

I did find this:

Typically, spooling operations (such as Lazy Spool or Eager Spool) occur
when an input rowset to a join or some other operation is too large for storage
in RAM. When this happens, the DBMS will write (or spool) the large rowset
onto hard disk storage in tempdb - this is kind of like the Windows swapfile.

how many rows in that table?

[disruptivehair]

Where CITYPE is NOT NULL

Is CITYPE an INDEX? And it allows NULL's? I'm not so sure how NULL's are handled in INDEX SCANS - I'm not sure I have ever indexed a column that allows null's...

CI_Type is not indexed.

I did find this:
how many rows in that table?

In the table where all the data is actually held there are 528,643 rows.

[szlamany]

So a table scan is being done for that query.

Is the PRIMARY KEY a CLUSTERED INDEX?

[disruptivehair]

So a table scan is being done for that query.

It doesn't say a table scan is being done. I hate computers sometimes. I mean what am I, psychic?

Is the PRIMARY KEY a CLUSTERED INDEX?

Yes.

[szlamany]

...what am I, psychic?

I am

Actually, since you told me that the WHERE column was not an INDEX...

but I can see that 87% of the time was spent on something appearing to be named "PPEKBaseData.PK..."

that means you have a CLUSTERED PRIMARY KEY.

So your DATA LEAVES are in physically stored in with your PRIMARY INDEX LEAVES. You have no DATA section - so the PRIMARY KEY section was used.

It has no relationship with CI_Type so it was "table scanned"...

I'm guessing based on the number of rows+size of row data returned that the LAZY spooler was used to store the working resultset on disk - not enough memory space...

[szlamany]

[edit]Change it to:

Select * From dbo.vwfSpecialIssued
	Where PRIMARYKEYCOLUMN is in (Select PRIMARYKEYCOLUMN
					From dbo.vwfSpecialIssued
					Where CI_Type is not null)

and tell me if performance changes

[disruptivehair]

[edit]Change it to:

Select * From dbo.vwfSpecialIssued
	Where PRIMARYKEYCOLUMN is in (Select PRIMARYKEYCOLUMN
					From dbo.vwfSpecialIssued
					Where CI_Type is not null)

and tell me if performance changes

Nope, no change.

This:

Select * From dbo.vwfeSpecialIssued
	Where Pol_no in (Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null)

and this:

SELECT * FROM dbo.vwFESpecialIssued
WHERE CI_Type IS NOT NULL

took exactly the same amount of time to run though their execution plans look different. The one you wrote contains a clustered index seek and an index scan as well as three clustered index scans; the simple one below just has three clustered index scans.

[szlamany]

You did see my post #19 - right?

At any rate - I was trying to avoid the spool to disk by using the subquery to generate a "primary key list" - being smaller than SELECT *...

So that the final resultset would be served by an index scan.

But "IN (subquery)" is a stinky construct.

What's the goal here anyway? There is no way to really avoid a table scan when the WHERE clause is on some un-keyed column of data. Do you have the option of temporarily adding an INDEX for ci_type to see the difference - or do you need DBA blessing for that

[disruptivehair]

You did see my post #19 - right?

A better question to ask would be 'did you understand my post #19,' the answer to which is a resounding NO!

At any rate - I was trying to avoid the spool to disk by using the subquery to generate a "primary key list" - being smaller than SELECT *...

So that the final resultset would be served by an index scan.

Buuuuh ok.

But "IN (subquery)" is a stinky construct.

Er...if you say so.

What's the goal here anyway? There is no way to really avoid a table scan when the WHERE clause is on some un-keyed column of data. Do you have the option of temporarily adding an INDEX for ci_type to see the difference - or do you need DBA blessing for that

No, in the dev DBs I can do pretty much whatever I want. I tried the query with and without an index on CI_Type and the performance was identical.

Maybe my DB just sucks.

[szlamany]

"Where ... IN (subquery)" stinks because each and every row in the FROM table is checked against existence in a subquery. This is not a very natural way to JOIN things together. I do use it myself sometimes - but avoid it if possible. Sub-queries in general are poor performer's...

I believe a point was made by Kaffenils in a post last week that putting an INDEX in for a WHERE column really won't help if doing a SELECT * since it's got to use the DATA section itself for so much of the work - so it ends up going there instead and potentially ignoring the INDEX you put for the WHERE column.

I'm not sure that an INDEX with NULL's involved and a WHERE COLX IS NOT NULL would ever work anyway - NULL's being equal to nothing type issue.

My point from back in #19 was that you have no PRIMARY KEY that sits by itself. When you CLUSTER your primary key you are actually taking the DATA portion - the actual data rows - and ordering them in PRIMARY KEY order. So your PRIMARY KEY section physically resides on disk, in the database, with you physical DATA portion. So every access to a table that has as PRIMARY KEY that is CLUSTERED is against the PK section of the table - since that's where the data resides.

Since your column of WHERE (ci_type) is not in order - not being ordered by the primary key - a "full scan" of the data is done to find rows matching your where clause. Since the "data" resides in the primary key (because it's clustered) then it might appear in the execution plan that it used the PK, but not in an "index" scan kind of way - but really in a table scan kind of way.

Hope that makes more sense.

Try this:

Create View dbo.MyKeys_V as Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null

Then do:

Select * From dbo.MyKeys_V MK
      Left Join dbo.vwfeSpecialIssued SI on SI.Pol_No=MK.Pol_No

and see if that works differently...

[disruptivehair]

"Where ... IN (subquery)" stinks because each and every row in the FROM table is checked against existence in a subquery. This is not a very natural way to JOIN things together. I do use it myself sometimes - but avoid it if possible. Sub-queries in general are poor performer's...

I believe a point was made by Kaffenils in a post last week that putting an INDEX in for a WHERE column really won't help if doing a SELECT * since it's got to use the DATA section itself for so much of the work - so it ends up going there instead and potentially ignoring the INDEX you put for the WHERE column.

I'm not sure that an INDEX with NULL's involved and a WHERE COLX IS NOT NULL would ever work anyway - NULL's being equal to nothing type issue.

My point from back in #19 was that you have no PRIMARY KEY that sits by itself. When you CLUSTER your primary key you are actually taking the DATA portion - the actual data rows - and ordering them in PRIMARY KEY order. So your PRIMARY KEY section physically resides on disk, in the database, with you physical DATA portion. So every access to a table that has as PRIMARY KEY that is CLUSTERED is against the PK section of the table - since that's where the data resides.

Since your column of WHERE (ci_type) is not in order - not being ordered by the primary key - a "full scan" of the data is done to find rows matching your where clause. Since the "data" resides in the primary key (because it's clustered) then it might appear in the execution plan that it used the PK, but not in an "index" scan kind of way - but really in a table scan kind of way.

Hope that makes more sense.

Sort of, but not really. I hate reading about indexing because it sounds like such a pain in the arse. I still try lots of different things and they continue to NOT work, and I'm beginning to wonder what the point of indexing is at all since I've never seen it improve my DB's performance.

Try this:

Create View dbo.MyKeys_V as Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null

Then do:

Select * From dbo.MyKeys_V MK
      Left Join dbo.vwfeSpecialIssued SI on SI.Pol_No=MK.Pol_No

and see if that works differently...

It's slower than the last query you suggested. The previous one took 3 seconds to run, this one takes 5 and its execution plan looks like spaghetti. Doesn't really mean much to me.

[szlamany]

I'm struggling to come up with a way to identify the sub-set of rows you want in the 500,000 rows that exists when there appears to be no INDEX possible.

Let's try this just to see if we can get it to work differently - since you are in a development DB, I'm guessing you can create a table to test things with...

Create Table MyKeys (Pol_no somedatatype Primary Key Clustered)

Insert into MyKeys Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null

Do this outside of checking the execution plan - I just want to get a table of the primary keys desired.

Then do this in a new window - checking the execution plan.

Select SI.* From dbo.MyKeys MK
      Left Join dbo.vwfeSpecialIssued SI on SI.Pol_No=MK.Pol_No

[disruptivehair]

I'm struggling to come up with a way to identify the sub-set of rows you want in the 500,000 rows that exists when there appears to be no INDEX possible.

Let's try this just to see if we can get it to work differently - since you are in a development DB, I'm guessing you can create a table to test things with...

Create Table MyKeys (Pol_no somedatatype Primary Key Clustered)

Insert into MyKeys Select Pol_no
			From dbo.vwfeSpecialIssued
			Where CI_Type is not null

Do this outside of checking the execution plan - I just want to get a table of the primary keys desired.

Then do this in a new window - checking the execution plan.

Select SI.* From dbo.MyKeys MK
      Left Join dbo.vwfeSpecialIssued SI on SI.Pol_No=MK.Pol_No

It doesn't look any different from the first one.

http://i16.photobucket.com/albums/b2...utionplan2.jpg

[szlamany]

It doesn't look any different from the first one.

http://www.vbforums.com/images/ieimages/2006/11/1.jpg

Did it run any faster?

[disruptivehair]

Did it run any faster?

Nope...took three seconds.

[wild_bill]

I've recreated one of your routines using an enum as an input parameter. This will prevent misspelling a valid option, and will use intelisense so you don't need to find valid options when using it.

' This function uses the tab the user is on and the value of the filter they've
    ' selected to construct part of the 'WHERE' clause SQL Server will need to return
    ' the appropriate data.  Example 'filter' values are York, Bankhall, CONTROL, etc.
    ' This function does not actually include 'WHERE' or 'AND'; that part of the string
    ' is constructed by another function.
 
    Public Function GetClause(ByVal columnName As Columns, ByVal strFilterValue As String) As String
 
        ' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
        ' is closed then open it.
        ' Call CheckConnection
 
        Dim strClause As String
 
        ' Builds a SQL clause based on what tab the user is currently using; if the tab value
        ' is "Headof" then no clause is built as no filter is actually being used.
        Select Case columnName
            Case Columns.HeadOf
                strClause = ""
            Case Columns.Distributor
                strClause = "Distributor = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Site
                strClause = "Site = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Team
                strClause = "Team = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Parent
                strClause = "ParentCompany = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Other
                strClause = ""
        End Select
 
        GetClause = strClause
 
    End Function
    Public Enum Columns
        HeadOf
        Distributor
        Site
        Team
        Parent
        Other
    End Enum

[disruptivehair]

I've recreated one of your routines using an enum as an input parameter. This will prevent misspelling a valid option, and will use intelisense so you don't need to find valid options when using it.

' This function uses the tab the user is on and the value of the filter they've
    ' selected to construct part of the 'WHERE' clause SQL Server will need to return
    ' the appropriate data.  Example 'filter' values are York, Bankhall, CONTROL, etc.
    ' This function does not actually include 'WHERE' or 'AND'; that part of the string
    ' is constructed by another function.
 
    Public Function GetClause(ByVal columnName As Columns, ByVal strFilterValue As String) As String
 
        ' Check to make sure connection still exists.  If it doesn't, set it up.  If connection
        ' is closed then open it.
        ' Call CheckConnection
 
        Dim strClause As String
 
        ' Builds a SQL clause based on what tab the user is currently using; if the tab value
        ' is "Headof" then no clause is built as no filter is actually being used.
        Select Case columnName
            Case Columns.HeadOf
                strClause = ""
            Case Columns.Distributor
                strClause = "Distributor = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Site
                strClause = "Site = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Team
                strClause = "Team = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Parent
                strClause = "ParentCompany = " & "'" & "'" & strFilterValue & "'" & "'"
            Case Columns.Other
                strClause = ""
        End Select
 
        GetClause = strClause
 
    End Function
    Public Enum Columns
        HeadOf
        Distributor
        Site
        Team
        Parent
        Other
    End Enum

Hey, that's neat. I haven't really used enums before. Right now we're in the midst of a redesign test that may change some things; the DBAs keep asking us to hit a moving target so this code may change. This is cool though.