Click here to Skip to main content
15,037,168 members
Home / Discussions / Database
   

Database

 
QuestionMax. number of concurrent user in PostGre database Pin
Navin Pandit17-Aug-11 18:55
MemberNavin Pandit17-Aug-11 18:55 
AnswerRe: Max. number of concurrent user in PostGre database Pin
Wendelius17-Aug-11 19:00
mveWendelius17-Aug-11 19:00 
GeneralRe: Max. number of concurrent user in PostGre database Pin
Navin Pandit17-Aug-11 19:10
MemberNavin Pandit17-Aug-11 19:10 
GeneralRe: Max. number of concurrent user in PostGre database Pin
Wendelius17-Aug-11 19:35
mveWendelius17-Aug-11 19:35 
GeneralRe: Max. number of concurrent user in PostGre database Pin
jschell18-Aug-11 13:02
Memberjschell18-Aug-11 13:02 
GeneralRe: Max. number of concurrent user in PostGre database Pin
Navin Pandit18-Aug-11 18:33
MemberNavin Pandit18-Aug-11 18:33 
GeneralRe: Max. number of concurrent user in PostGre database Pin
jschell19-Aug-11 7:34
Memberjschell19-Aug-11 7:34 
QuestionSearching with optional parameters [modified] Pin
Pascal Ganaye16-Aug-11 1:36
MemberPascal Ganaye16-Aug-11 1:36 
In most application I have seen code that searches records using a number of optional fields.
I have to build such a screen with a table that is expected to contains several million records.

Here a short example that exposes my questions, I would like to here your comments on it:

CREATE	PROCEDURE SearchContacts
(
	@Reference varchar(8) = NULL,
	@DateOfBirth datetime = NULL,
	@ContactName varchar(50) = NULL,
	@ContactType varchar(10) = NULL
)
AS
BEGIN
  SELECT TOP 100 * FROM Contacts WHERE
           (@Reference IS NULL OR Contacts.Reference = @Reference)
           AND (@DateOfBirth IS NULL OR Contacts.DateOfBirth = @DateOfBirth)
           AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
           AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
END


This query is very simple however it is probably not going to take advantage of the indexes that are available. As I have to deal with a table that will contain a lot of record, this won't be acceptable.
An alternative would be to hard code many if :
IF (@ContactName IS NULL AND @ContactType IS NULL AND @Reference IS NOT NULL) 
BEGIN
  SELECT * FROM Contacts WHERE
           (@Reference IS NULL OR Contacts.Reference = @Reference)
           AND (@DateOfBirth IS NULL OR Contacts.DateOfBirth = @DateOfBirth)
END
ELSE IF (@Reference IS NULL AND @DateOfBirth IS NULL) 
BEGIN
  SELECT * FROM Contacts WHERE
           (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
           AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
END
...

This can probably use an index but it is very tedious to write in this example the 16 combination of parameters that can happen.

The next step is to try with a dynamic query
SET @Sql = 'SELECT  TOP 100 * FROM Contacts WHERE TRUE '
IF @Reference IS NOT NULL
BEGIN 
  SET @Sql = @Sql + ' AND Contacts.Reference = ''' + QUOTENAME(@Reference,'') + ''''
END
IF @DateOfBirth IS NOT NULL
BEGIN 
  SET @Sql = @Sql + ' AND Contacts.DateOfBirth = ''' +  QUOTENAME(@DateOfBirth,'') + ''''
END
...

On this last sample I assume this will allow the optimizer to use the right index.
But will the optimization would have to be done on every request?

Is there a more elegant way to write this sort of queries while keeping maximum efficiency ?

modified on Tuesday, August 16, 2011 11:34 AM

AnswerRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 3:23
professionalPIEBALDconsult16-Aug-11 3:23 
GeneralRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 4:16
MemberPascal Ganaye16-Aug-11 4:16 
AnswerRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 4:21
MemberPascal Ganaye16-Aug-11 4:21 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 17:56
professionalPIEBALDconsult16-Aug-11 17:56 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes16-Aug-11 18:06
professionalMycroft Holmes16-Aug-11 18:06 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 2:48
professionalPIEBALDconsult17-Aug-11 2:48 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes17-Aug-11 5:40
professionalMycroft Holmes17-Aug-11 5:40 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 14:11
professionalPIEBALDconsult17-Aug-11 14:11 
GeneralRe: Searching with optional parameters Pin
S Douglas20-Aug-11 7:53
professionalS Douglas20-Aug-11 7:53 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult20-Aug-11 13:01
professionalPIEBALDconsult20-Aug-11 13:01 
QuestionRe: Searching with optional parameters Pin
Shameel16-Aug-11 4:52
professionalShameel16-Aug-11 4:52 
AnswerRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 6:02
MemberPascal Ganaye16-Aug-11 6:02 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 18:04
professionalPIEBALDconsult16-Aug-11 18:04 
QuestionSQL bit to bool conversion [moved] Pin
5fingers16-Aug-11 0:37
Member5fingers16-Aug-11 0:37 
GeneralRe: SQL bit to bool conversion Pin
Nagy Vilmos16-Aug-11 0:39
professionalNagy Vilmos16-Aug-11 0:39 
GeneralRe: SQL bit to bool conversion Pin
Shameel16-Aug-11 4:53
professionalShameel16-Aug-11 4:53 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 0:59
Member5fingers17-Aug-11 0:59 

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.