Click here to Skip to main content
15,499,594 members
Home / Discussions / Database
   

Database

 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes16-Aug-11 19:06
professionalMycroft Holmes16-Aug-11 19:06 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 3:48
professionalPIEBALDconsult17-Aug-11 3:48 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes17-Aug-11 6:40
professionalMycroft Holmes17-Aug-11 6:40 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 15:11
professionalPIEBALDconsult17-Aug-11 15:11 
GeneralRe: Searching with optional parameters Pin
S Douglas20-Aug-11 8:53
professionalS Douglas20-Aug-11 8:53 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult20-Aug-11 14:01
professionalPIEBALDconsult20-Aug-11 14:01 
QuestionRe: Searching with optional parameters Pin
Shameel16-Aug-11 5:52
professionalShameel16-Aug-11 5:52 
AnswerRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 7:02
MemberPascal Ganaye16-Aug-11 7:02 
I am trying to stay database agnostic on this, I might be wrong on some databases.

When you write the query below in a stored procedure, the database engine will typically build an execution plan for it.
SQL
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)

At that time it will still have to decide which index to use.
Whatever choice it makes at compile time is doomed to be wrong.
The optimizer can't choose the right index, because there is not right index.
It would have to choose the index that is right with a set of given parameters.
Even if it were to delay the choice till the first use of the procedure, then the second use might be done with different parameters and another index should be used.

From what I can see in MS SQL2008, the execution plan is invariably a Clustered Index Scan.
Index Scan meaning that it will read the entire index, from A to Z. This can be terribly slow on a very large table.

If the query is dynamic however then the query becomes a lot simpler.
SQL
SELECT TOP 100 * FROM Contacts WHERE
(Reference = @Reference)
AND (DateOfBith = @DateOfBith)

The 'OR' disappear. The execution plan then is a lot better. Typically it will use an Index Seek which is only a few disk access and not a full read.

If you use SQL Server Management Studio you can check that using the 'Include Actual Execution Plan' button.
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 19:04
professionalPIEBALDconsult16-Aug-11 19:04 
QuestionSQL bit to bool conversion [moved] Pin
5fingers16-Aug-11 1:37
Member5fingers16-Aug-11 1:37 
GeneralRe: SQL bit to bool conversion Pin
Nagy Vilmos16-Aug-11 1:39
professionalNagy Vilmos16-Aug-11 1:39 
GeneralRe: SQL bit to bool conversion Pin
Shameel16-Aug-11 5:53
professionalShameel16-Aug-11 5:53 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 1:59
Member5fingers17-Aug-11 1:59 
GeneralRe: SQL bit to bool conversion Pin
Dalek Dave16-Aug-11 1:39
professionalDalek Dave16-Aug-11 1:39 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 1:57
Member5fingers17-Aug-11 1:57 
GeneralRe: SQL bit to bool conversion Pin
chriselst16-Aug-11 1:40
Memberchriselst16-Aug-11 1:40 
GeneralRe: SQL bit to bool conversion Pin
CPallini16-Aug-11 1:45
mveCPallini16-Aug-11 1:45 
GeneralRe: SQL bit to bool conversion Pin
PompeyBoy316-Aug-11 1:45
MemberPompeyBoy316-Aug-11 1:45 
GeneralRe: SQL bit to bool conversion Pin
Richard MacCutchan16-Aug-11 2:15
mveRichard MacCutchan16-Aug-11 2:15 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 1:56
Member5fingers17-Aug-11 1:56 
GeneralRe: SQL bit to bool conversion Pin
Richard MacCutchan17-Aug-11 2:27
mveRichard MacCutchan17-Aug-11 2:27 
GeneralRe: SQL bit to bool conversion Pin
Smithers-Jones16-Aug-11 2:03
MemberSmithers-Jones16-Aug-11 2:03 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 1:56
Member5fingers17-Aug-11 1:56 
QuestionLocal Variable Ntext Probelm sql 2005 Pin
md_refay14-Aug-11 2:10
Membermd_refay14-Aug-11 2:10 
Answercross-post Pin
Wendelius14-Aug-11 2:45
mveWendelius14-Aug-11 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.