Cross Join taking too long

Question

Please see the DDL below:

CREATE TABLE [dbo].[TBX_RRDGenieDeletedItem](
    [DeletedId] [decimal](25, 0) NOT NULL
) ON [PRIMARY]

INSERT INTO TBX_RRDGenieDeletedItem values (90309955000010401948421)

CREATE TABLE [dbo].[dbNicheCIS](
    [OccurrenceID] [decimal](25, 0) NULL,
    [OccurrenceFileNo] [varchar](20) NULL
)

INSERT INTO dbNicheCIS values (90309955000010401948421,'3212')

CREATE TABLE [dbo].[Asset_Table](
    [user_crimenumber] [varchar](4000) NOT NULL
)

INSERT INTO Asset_Table VALUES ('3212; 4512; 34322; 45674; 33221')

The only table I designed was dbNicheCIS. I am trying to find all of the rows in tbx_rrdgeniedeleteditem that are also in Asset_Table using the LIKE statement. Asset_Table contains the OccurrenceFileNo (note that asset table contains occurrencefileno: 3212, which relates to OccurrenceID: 90309955000010401948421). I have tried this:

Select user_crimenumber from tbx_rrdgeniedeleteditem --asset_table.user_crimenumber 
inner join dbNicheCIS on tbx_rrdgeniedeleteditem.deletedid = dbNicheCIS.OccurrenceID
cross join asset_table
where deletedid like '903%' and asset_table.user_crimenumber like '%' + occurrencefileno + '%'

It works, but it takes hours to run. Is there a better way to approach it rather than a cross join?

@Dudi Konfino, AssetTable contains millions on rows. It is too slow. — w0051977, Nov 10 '14 at 19:43
You might find an approach that runs, "a bit faster" than what you have. But I doubt you will find an "efficient" approach. Perhaps you can bring this to your supervisor as a use-case to properly normalize the tables? You could have an efficient query if Asset_Table were properly normalized. (If disk space is not a concern, maybe create a new reporting table that builds nightly and is a normalized version of Asset_Table?) — DeadZone, Nov 10 '14 at 21:02

radar · Answer 1 · 2014-11-10T18:36:21.720

0

You can use INNER JOIN and also you can eliminate LIKE for the number comparison like below

Select user_crimenumber from tbx_rrdgeniedeleteditem
inner join dbNicheCIS 
on tbx_rrdgeniedeleteditem.deletedid = dbNicheCIS.OccurrenceID
inner join asset_table
on CAST(LEFT([DeletedId], 3) AS [decimal](25, 0)) =903 
and asset_table.user_crimenumber like '%' + occurrencefileno + '%'

edited Nov 10 '14 at 18:36

answered Nov 10 '14 at 18:19

radar

13,270
2
25
33

This looks promising. I will try it shortly and get back to you. Thanks. – w0051977 Nov 10 '14 at 18:36
I am afraid this will not help too much. This query produces identical execution plan as the OP's query. See http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join – Vojtěch Dohnal Nov 10 '14 at 18:57
It is too slow. Thanks anyway. – w0051977 Nov 10 '14 at 19:42
@w0051977, index won't either help as you are having % in the front too. better to create a seperate table for comma seperated values like assocatin table. – radar Nov 10 '14 at 19:52

Steve · Answer 2 · 2014-11-10T18:41:46.177

0

you can make use of the in operator in this case

SELECT * FROM TBX_RRDGenieDeletedItem
WHERE DeletedId IN (
SELECT DISTINCT OccurrenceID FROM dbNicheCIS
INNER JOIN Split(...) ON ...)

Updated: You can create a custom split function which will split the values into a temp table and then do the join.

edited Nov 10 '14 at 18:41

answered Nov 10 '14 at 18:23

Steve

11,696
7
43
81

Please reread the question. Usercrimenumber contains delimited values. This will not work. Thanks anyway. – w0051977 Nov 10 '14 at 18:35
@w0051977 updated, you can create a custom split function to deal with this case – Steve Nov 10 '14 at 18:42

score 0 · Answer 3 · edited May 23 '17 at 12:12

You need to index your tables to get faster query response.

CREATE INDEX [IX_dbNicheCIS_OccurrenceID] ON [dbNicheCIS]
([OccurrenceID] ASC, [OccurrenceFileNo] ASC)

CREATE INDEX [IX_TBX_RRDGenieDeletedItem_DeletedId] ON [dbo].[TBX_RRDGenieDeletedItem]
([DeletedId] ASC)

Creating such indexes replaces "Table scan" in query execution plan with faster "Index scan" and "Index seek". But you cannot solve like '%' + occurrencefileno + '%' problem with simple indexes. There you will have to use full text indexes. After you define fulltext index on asset_table.user_crimenumber, you can use following query

SELECT user_crimenumber 
FROM tbx_rrdgeniedeleteditem  di --asset_table.user_crimenumber 
JOIN dbNicheCIS dnc
ON di.deletedid = dnc.OccurrenceID
CROSS JOIN asset_table at
WHERE di.deletedid like '903%' 
AND CONTAINS(at.user_crimenumber, occurrencefileno)

But it is a bad practice to store your occurrencefileno list as a varchar value delimited with semicolons. If you were the author of this database design, you should have tried to normalize the data, so that you got one row for every occurrencefileno and not a string like '3212; 4512; 34322; 45674; 33221'.

You can also create as a first step before querying a normalized version of asset_table.user_crimenumber and then use this table with normal indexes as a base for your further queries.

To split your asset_table.user_crimenumber fields you can use the Fn_Split() function as mentioned in this answer.

There is also option using the fnSplit to rewrite your query this way:

SELECT user_crimenumber 
FROM tbx_rrdgeniedeleteditem  di --asset_table.user_crimenumber 
JOIN dbNicheCIS dnc
ON di.deletedid = dnc.OccurrenceID
INNER JOIN (
    SELECT at.user_crimenumber, f.item FROM asset_table at 
    CROSS APPLY dbo.fnSplit(at.user_crimenumber,';') f   ) at
ON at.item=dnc.occurrencefileno
WHERE di.deletedid like '903%'

If you create fnSplit as CLR in C# as described here you may get even faster results. But it will not speed up your query magically.

Cross Join taking too long

3 Answers3