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.

UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.sgenavn),
MATCH_basistabel.sgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.sgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
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.organisati onsnummer),
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)Hi

What data type is ALL_tbl_medlemsinfo.medlemsnummer?|||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.|||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?

?|||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.


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|||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.|||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

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.|||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 :D ).

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?|||thanks.that was mistake. I will correct it|||When I get access to a SQL Server I will experiment with the code you posted.|||Hi Mallier

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

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

Checking a hunch....|||Blimey - a hunch paid off - that deserves framing.

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?|||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'|||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):

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?|||Okay, so here's returning to my code..

Here are indexes on ALL_tbl_medlemsinfo

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]([sgenavn]) 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

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...|||Hi

What data type is ALL_tbl_medlemsinfo.medlemsnummer?|||There is no short answer to get perfomance improvement.u have to try different indexes.So post ur DDL of tables too.|||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?

This is because non-clustered indexes use the clustered index to locate data rows and because non-clustered indexes must hold the clustered keys within their B-tree structures. This helps to reduce not only the size of the clustered index, but all non-clustered indexes on the table as well