Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Can this be done faster?

    I was just wondering if this can be done any faster? code-wise that is...

    Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.


    Code:
    UPDATE MATCH_basistabel 
    	SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
    	MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
    	MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnavn),
    	MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnavn2),
    	MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
    	MATCH_basistabel.nationalitet =  convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
    	MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisationsnummer),
    	MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
    	MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
    	MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
    	MATCH_basistabel.franavision = 1
    FROM MATCH_basistabel, ALL_tbl_medlemsinfo 
    WHERE isnumeric(matchfelt) = 1
    AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer) 
    AND MATCH_basistabel.franavision = 0)
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    What data type is ALL_tbl_medlemsinfo.medlemsnummer?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    u havent mentioned about index on those two table .It will helpfull if u can post about indexes on those table.My suggestion is create index on MATCH_basistabel.matchfelt,MATCH_basistabel.franav ision and ALL_tbl_medlemsinfo.medlemsnummer if it is not there.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mallier
    u havent mentioned about index on those two table .It will helpfull if u can post about indexes on those table.My suggestion is create index on MATCH_basistabel.matchfelt,MATCH_basistabel.franav ision and ALL_tbl_medlemsinfo.medlemsnummer if it is not there.
    Hi Mallier

    Just testing my index knowledge here so correct me if I'm wrong:
    ALL_tbl_medlemsinfo.medlemsnummer is converted so not sargable.
    MATCH_basistabel.franavision looks like a Bit so probably any index would be ignored.
    MATCH_basistabel.matchfelt is also converted and I presume (though don't know) that an index doesn't help an IsNumeric test?

    ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Cool

    try this example and see the difference,its better than explaining in words.it will be greatfull if other experts can give more input on this.
    Code:
     
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    go
    set nocount on
    go
    Create table indextest ( Gender bit, lastname varchar(100))
    go
    Declare @i int
    declare @lastname varchar(100)
    Set @i = 0
    While @i < 10000
    Begin
     set @lastname='joe'+cast(@i as varchar)
                Insert into indextest(Gender,lastname) values('1',@lastname)
                Set @i = @i + 1
    end
    Insert into indextest(Gender,lastname) values('0','foo1')
    Insert into indextest(Gender,lastname) values('0','foo2')
    ------------free cache-----------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    go
     
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='foo2'
    where convert(varchar(100),lastname)='foo3' and Gender='0'
    go
    ---- see the result ,u can see table scan ,note down the cpu,elapsed time,logical read etc---------
     
     
    -----now create index-------
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    Create index ind_1 on indextest(Gender)
    Create index ind_2 on indextest(lastname)
    go
    ---- remove cache--------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    --------run the query again----
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='foo2'
    where convert(varchar(100),lastname)='foo3' and Gender='0'
    ---- see the result ,u can see index scan not table scan,note down cpu,elapsed time,logical read etc---------
     
    drop table indextest
    Last edited by mallier; 01-16-06 at 10:29.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    mallier, you are inserting 10,000 "1" bit values, and only 2 "0" bit values. That is pretty high selectivity for a bit value, particularly one that represents gender. Trya more typical cardinality by inserting half 0, and half 1 (you could use @i MOD 2 to distribute them.

    I'm not at a SQL Server right now, but I think you will get a different result.

    Trinsan, optimizing that is going to be very difficult because of all the non-sargable joins. Consider creating the recordset as an indexed view if you data extract speed is a high priority. Unfortunately, you are dealing with dirty data and you are being forced to cleanse it not once, but every time the query runs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by blindman
    mallier, you are inserting 10,000 "1" bit values, and only 2 "0" bit values. That is pretty high selectivity for a bit value, particularly one that represents gender. Trya more typical cardinality by inserting half 0, and half 1 (you could use @i MOD 2 to distribute them.

    I'm not at a SQL Server right now, but I think you will get a different result.

    Trinsan, optimizing that is going to be very difficult because of all the non-sargable joins. Consider creating the recordset as an indexed view if you data extract speed is a high priority. Unfortunately, you are dealing with dirty data and you are being forced to cleanse it not once, but every time the query runs.

    I tried as per u mentioned
    Code:
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    set nocount on
    go
    Create table indextest ( Gender Char(1), lastname varchar(100))
    go
    Declare @i int
    declare @lastname varchar(200)
    Set @i = 0
    While @i < 10000
    Begin
         set @lastname='joe'+cast(@i as varchar)
                Insert into indextest(Gender,lastname) values('1',@lastname)
                Set @i = @i + 1
    end
    go
    Declare @i int
    declare @lastname varchar(200)
    Set @i = 0
    While @i < 10000
    Begin
         set @lastname='foo'+cast(@i as varchar)
                Insert into indextest(Gender,lastname) values('0',@lastname)
                Set @i = @i + 1
    end
    ------------free cache-----------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    go
    ---------------------------------
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='foo3'
    where convert(varchar(100),lastname)='foo2' and Gender='0'
    go
    -----------------------------
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    Create index ind_1 on indextest(Gender)
    Create index ind_2 on indextest(lastname)
    go
    ---- remove cache--------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    --------run the query again----
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='foo2'
    where convert(varchar(100),lastname)='foo3' and Gender='0'
    go
    drop table indextest
    go
    --statistics without index
    SQL Server parse and compile time:
    CPU time = 125 ms, elapsed time = 241 ms.
    Table 'indextest'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0.
    SQL Server Execution Times:
    CPU time = 30 ms, elapsed time = 30 ms.
    (4 row(s) affected)

    SQL Server Execution Times:
    CPU time = 31 ms, elapsed time = 30 ms.
    SQL Server Execution Times:
    CPU time = 31 ms, elapsed time = 31 ms.

    update indextest set lastname='foo3' where convert(varchar(100),lastname)='foo2' and Gender='0'
    |--Table Update(OBJECT[CDM_IMPORT_TECCI].[dbo].[indextest]), SET[indextest].[lastname]='foo3'))
    |--Top(ROWCOUNT est 0)
    |--Table Scan(OBJECT[CDM_IMPORT_TECCI].[dbo].[indextest]), WHERE[indextest].[lastname]='foo2' AND [indextest].[Gender]='0') ORDERED)

    -----statistics with index----------

    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 51 ms.
    Table 'indextest'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
    Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 3 ms.
    (7 row(s) affected)

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 3 ms.
    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 17 ms.


    update indextest set lastname='foo2' where convert(varchar(100),lastname)='foo3' and Gender='0'
    |--Table Update(OBJECT[CDM_IMPORT_TECCI].[dbo].[indextest]), SET[indextest].[lastname]='foo2'))
    |--Top(ROWCOUNT est 0)
    |--Filter(WHERE[indextest].[lastname]='foo3'))
    |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[CDM_IMPORT_TECCI].[dbo].[indextest]))
    |--Index Seek(OBJECT[CDM_IMPORT_TECCI].[dbo].[indextest].[ind_1]), SEEK[indextest].[Gender]='0') ORDERED FORWARD)
    ------------------------------------------------------

    blindman,It will helpful if u comment more on this topic.
    Last edited by mallier; 01-16-06 at 11:44.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mallier

    Well - that seems to blow my carefully acquired understanding of indexes out of the water. I thought the cardinality business mucked up the index too (although I didn't know the word ).

    There is one discrepancy - you sure all this code matches? You insert 'M' and 'F' into varchar fields and then search for '0' or is that deliberate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Posts
    269
    thanks.that was mistake. I will correct it
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When I get access to a SQL Server I will experiment with the code you posted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mallier

    Had a quick play. If you change the where to:

    Code:
    where Gender='0'
    then it does table scan.
    Code:
    where convert(varchar(100),lastname)='foo3'
    results in an index seek.

    Checking a hunch.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey - a hunch paid off - that deserves framing.

    Code:
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    set nocount on
    go
    Create table indextest ( Gender Char(1), lastname VarChar(100))
    go
    Declare @i int
    Set @i = 0
    While @i < 10000
    Begin
                Insert into indextest(Gender,lastname) values('1',CAST(@i AS varchar(100)))
                Set @i = @i + 1
    end
    go
    Declare @i int
    Set @i = 0
    While @i < 10000
    Begin
                Insert into indextest(Gender,lastname) values('0',CAST(@i AS varchar(100)))
                Set @i = @i + 1
    end
    ------------free cache-----------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    go
    ---------------------------------
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='3'
    where convert(int,lastname)=2
    go
    -----------------------------
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    Create index ind_1 on indextest(Gender)
    Create index ind_2 on indextest(lastname)
    go
    ---- remove cache--------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    --------run the query again----
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    update indextest set lastname='2'
    where convert(int,lastname)=3
    go
    drop table indextest
    go
    --statistics without index
    This matches Trinsan's senario more closely as he is changing the data type (not just the size) of the data. This results in a table scan.

    Looks like SQL Server can maintain the sargability if the size of the data changes so long as the data type remains the same.

    Am I on the right lines here?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    set nocount on
    go
    Create table indextest1 ( i int identity(1,1), Gender Char(1))
    go
    Declare @i int
    Set @i = 0
    While @i < 10000
    Begin
    Insert into indextest1(Gender) values('M')
    Set @i = @i + 1
    end
    Declare @i int
    Set @i = 0
    While @i < 10000
    Begin
    Insert into indextest1(Gender) values('F')
    Set @i = @i + 1
    end
    -----------------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    ------------------
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    Select * from indextest1 where Gender = 'M'
    -------
    -------now create index--------
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go

    Create clustered index clus_ind on indextest1(i)
    Create index ind on indextest1(Gender)
    ----------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    -----------
    Select * from indextest1 where Gender = 'M'
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mallier - I popped in some code and removed some more (duplicate declaration, no table drop and no second set of SET stats on etc. - nothing major basically):

    Code:
    set nocount on
    go
    Create table indextest1 ( i int identity(1,1), Gender Char(1))
    go
    Declare @i int
    Set @i = 0
    While @i < 10000
    Begin
    Insert into indextest1(Gender) values('M')
    Set @i = @i + 1
    end
    Set @i = 0
    While @i < 10000
    Begin
    Insert into indextest1(Gender) values('F')
    Set @i = @i + 1
    end
    -----------------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    ------------------
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    Select * from indextest1 where Gender = 'M'
    -------
    -------now create index--------
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    Create clustered index clus_ind on indextest1(i)
    Create index ind on indextest1(Gender)
    ----------
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    -----------
    Set statistics profile on
    Go
    Set statistics time on
    Go
    Set statistics io on
    Go
    Select * from indextest1 where Gender = 'M'
    Set statistics profile off
    Go
    Set statistics time off
    Go
    Set statistics io off
    Go
    Drop Table indextest1
    Coo - so add a clustered index and the optimiser seeks. Is this because the the clustered index is used as part of the gender index? Does adding a clustered index remove the cardinality issue?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Oct 2005
    Posts
    183
    Okay, so here's returning to my code..

    Here are indexes on ALL_tbl_medlemsinfo

    Code:
     CREATE  CLUSTERED  INDEX [IDX_all_tbl_medlemsinfo_mednr] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnummer]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_soege] ON [ket].[ALL_tbl_medlemsinfo]([søgenavn]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_nat] ON [ket].[ALL_tbl_medlemsinfo]([nationalitet]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_orgnr] ON [ket].[ALL_tbl_medlemsinfo]([organisationsnummer]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_ipdn] ON [ket].[ALL_tbl_medlemsinfo]([ipdn]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn2] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn2]) WITH  FILLFACTOR = 100 ON [PRIMARY]
    GO

    Aaaannnd... here are the indexes on MATCH_basistabel

    Code:
     CREATE  INDEX [IDX_match_basistabel_matchfelt] ON [dbo].[MATCH_basistabel]([matchfelt]) WITH  FILLFACTOR = 60 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_importkey] ON [dbo].[MATCH_basistabel]([importkey]) WITH  FILLFACTOR = 60 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_franavision] ON [dbo].[MATCH_basistabel]([franavision]) WITH  FILLFACTOR = 60 ON [PRIMARY]
    GO
    Maybe that'll give an insigt if the query I wrote can be improved....
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •