Skip to main content

Partial Search in sql server

Here I am not going to talk about fulltext search as generally in small applications which not that expensive servers we do not really go for full text indexes. Thus we are going to talk about wild card search.

Now a days providing search facility to end user has become quite normal. Users generally will have 'n' fields to search. They can search by providing values for all fields or they may provide values for only one field.

There are two solutions to this:-
The one i like -

I have not put any validation in place which we should as if there is no parameter specified the procedure will fail because of bad syntax as there will be a bad where clause. Thus check if no parameter is spec
CREATE PROCEDURE SearchProcedureName
        -- Add the parameters for the stored procedure here
        @Name VARCHAR(100),
        @Id VARCHAR(10),
        @error int output
AS
BEGIN
set @error = -1
if((ISNULL(@Name, '') ='') and (ISNULL(@Id, '') =''))
begin

return

end

begin try

declare @query varchar(max)
set @query = ''

set @query = 'SELECT   *
        FROM t_users
        where '

declare @var varchar(1000) = ''

if((ISNULL(@Name, '') <>''))
begin

if(isnull(@var,'') <> '')
set @var = ' and  userName LIKE ''%' + @Name + '%'' '
else
set @var = ' userName LIKE ''%' + @Name + '%'' '


set @query = @query + @var

end


if((ISNULL(@Id, '') <>''))
begin
if(isnull(@var,'') <> '')
set @var = ' and userId LIKE ''%' + @Id+ '%'' '
else
set @var = ' userId ''%' + @Id+ '%'' '

 
set @query = @query + @var

end


--        and so on....

exec(@query)
set @error = 0
end try
begin catch

--handle exception
end catch
END

And the one I do not like - 




CREATE PROCEDURE SearchProcedureName
        -- Add the parameters for the stored procedure here
        @Name VARCHAR(100),
        @Id VARCHAR(10),
     
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT  *
        FROM t_users
     
        WHERE ((ISNULL(@Name, '') = '') OR username LIKE '%' + @Name + '%')
        AND ((ISNULL(@Id, '') = '') OR userid LIKE '%' + @Id + '%')
     
END




The reason one should go for the first solution is:- In solution 2 wild card will be used even if the user do not provide any value for a particular parameter. This leads to lot of table scans. Over a period of time as the user base increases and as the data increases this hits the performance really badly.
Labels:

Comments

Post a Comment

Popular posts from this blog

Create a background / taskbar application in c# .NET

Recently, I was working on integration of two windows applications. First application will launch the second application on login and then they both will communicate using pre-defined set of instructions. There were some complications (I am not going into them) and thus we decided to have a third application which actually will act as mediator. First application will launch the mediator (third application) and it will launch the second application. For this purpose we needed to create a task bar application (which will run in background). How To ·          Create a new windows project and delete the default form (Form1). ·          In Program.cs create a new class and inherit it from Form. ·          Please refer the code below. ·          Now change the Main method. In Application.Run change the startup objec...
2 comments
Read more

Check SQL Server Job status (State) using sp_help_job and xp_sqlagent_enum_jobs

This article is about checking the status of a SQL job. In our work place we have lot of SQL jobs. These jobs will run whole day and are business critical. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not. All this can be achieved in SQL Server by using the procedures:- sp_help_job xp_sqlagent_enum_jobs Note: xp_sqlagent_enum_jobs is an undocumented proc inside of sp_help_job and is used extensively to get SQL agent job information. sp_help_job: This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc. Syntax sp_help_job { [ @job_id= ] jo...
2 comments
Read more

Java 8 JMX Default Metrics

This is more of a note. Here you can find default types and attributes for JMX on top of Java 8. Code: I will clean and explain it later :( private static void WriteAttributes(final MBeanServer mBeanServer, final ObjectName http) throws InstanceNotFoundException, IntrospectionException, ReflectionException { MBeanInfo info = mBeanServer.getMBeanInfo(http); MBeanAttributeInfo[] attrInfo = info.getAttributes(); System.out.println("Attributes for object: " + http +":\n"); for (MBeanAttributeInfo attr : attrInfo) { System.out.println(" " + attr.getName() + "\n"); } } Attributes for object: java.lang:type=MemoryPool,name=Metaspace:   Name   Type   Valid   Usage   PeakUsage   MemoryManagerNames   UsageThreshold   UsageThresholdExceeded   UsageThresholdCount   UsageThresholdSupported   CollectionUsageThreshold   Collectio...
Post a Comment
Read more