CODEWITHSUNDEEP
sqlsql-serversql-server-2012
RGS
RGS

Reputation: 5211

How to boost up SQL query execution time in SSMS?

I have used below query to fetch records from database. 

SELECT
    [dbo].[CamelCase](ISNULL(B.City, '')),
    CONVERT(VARCHAR(250), ISNULL(B.Addr1, '') + '/' + ISNULL(B.Addr2, '') + '/' + ISNULL(B.Area, '') + '/' + ISNULL(B.City, '') + '/' + ISNULL(B.State, '')),
    ISNULL(B.YardName, ''),
    DATEADD(HH, 10, CONVERT(DATETIME, A.Date_From)),
    DATEADD(HH, 17, CONVERT(DATETIME, A.Date_To)),
    '',
    ISNULL(B.Zone, ''),
    '',
    '',
    ISNULL(B.ContactPerson, ''),
    B.Mobile,
    ISNULL(B.EMailId, ''),
    '',
    A.AucCode,
    [dbo].[CamelCase](B.State),
    B.Pincode,
    [dbo].[CamelCase](ISNULL(B.City, ''))
FROM dbo.TBL_Auction A
JOIN dbo.TBL_PLACE B ON A.Auc_Place_Fk_Id = B.Place_Pk_Id

Indexes in TBL_Auction Table:

Index Name ------------------- + Column Name -------- + Index Type
PK__PASS_AUC__8BC43C38517CE882 |  Auc_Pk_Id           | CLUSTERED   
IX_PASS_Created_On             |  Created_On          | NONCLUSTERED
Unq_Pass_Auction               |  Auc_Code            | NONCLUSTERED
Unq_Pass_Auction               |  Auc_Place_Fk_Id     | NONCLUSTERED
FK_Pass_Place                  |  Auc_Place_Fk_Id     | NONCLUSTERED

Indexes in TBL_Place Table:

Index Name ------------------  + Column Name------- +  Index Type
PK__PASS_PLA__4F8634950F7A1AFB | Place_Pk_Id        |  CLUSTERED
IX_PASS_PLACE_I                | Place_Area         |  NONCLUSTERED
IX_PASS_PLACE_I                | Place_City         |  NONCLUSTERED
IX_PASS_PLACE_I                | Place_State        |  NONCLUSTERED
IX_PASS_PLACE_I                | Place_Country      |  NONCLUSTERED
IX_PASS_PLACE_I                | Place_Pincode      |  NONCLUSTERED
IX_PASS_PLACE_IV               | Place_Shrt_Code    |  NONCLUSTERED

But above query takes infinite time to return result.

I have created all necessary indexes for my joining tables. Even after that also optimizer chooses index scan instead of index seek. You can check the indexes list in my question for two tables. How to force the optimizer to choose index seek?

When I use Select * instead of specifying Select Column Names, at that time query result has been returned within 1 seconds. What is the problem when I specify column names in Select query?

Edit:-

ALTER FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END

enter image description here

Upvotes: 4

Views: 1359

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

Your camel case function will go into an infinite loop if passed NULL as the only way of exiting is PATINDEX('% %',@Str)= 0 and that will evaluate to unknown.

You need to fix that.

You can use RETURNS NULL ON NULL INPUT though for safety I would handle it explicitly too.

ALTER FUNCTION [dbo].[CamelCase] 
               (@Str VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH RETURNS NULL ON NULL INPUT, 
     SCHEMABINDING
AS
  BEGIN
      IF @Str IS NULL
        RETURN NULL;

        /*.... Rest of function*/

Upvotes: 4

Related Questions