CODEWITHSUNDEEP
sqlsql-server
Milacay
Milacay

Reputation: 1497

SQL Server - Better Way To Join Between VARCHAR and INT

I am looking for effective way to speed up my query. Let me describe the situation.

Every night, we dump reports from different vendors into a table. These are a lot of columns, but These columns below are the main columns that I use for joining in the query.

tblVendorShippingInfo (Note that all theses column below are VARCHAR since sometimes reports contains Varchar character even in QtyShipped) 

PONum     vSKU    vSKUDesc     ShipQuantity
P111111   A       Test1       5
P111111   A       Test1       2
P111111   B       Test3       5
P100002   C       Test4       6
P222222   D       Test5       7
P222222   D       Test5       6
P222222   E       Test6       7
PABC123   F       Test7       8
XYZ123    G       Test8       8
GHEHEH    H       Test9       8

tblPODetail -- Our table for Purchase Order. Note that the POID and DistyShippedQty are Integer. The POID is only 6 digit number.

POID      SKU     SKUDesc     DistyShippedQty
111111    A       Test1       
111111    B       Test3       
100002    C       Test4       
222222    D       Test5       
222222    E       Test6

If I use the query like this below to get and update the DistyShippedQty, it would work but slow because the conversion... Matching VARCHAR...etc.

select POID, SKU
, (
    SELECT SUM(ShipQuantity) AS ShipQuantity 
    FROM tblVendorShippingInfo
    WHERE substring(PONum, 2, 6) = Convert(varchar(10), pod.POID)
    AND vSKU = pod.SKU
    ) AS QtyCount
FROM tblPODetail pod

So, I am looking a better way to just filter the records in tblVendorShippingInfo table that only has PONum with 6 digits starting at 2nd position (after P, or any char), ignore all the records with chars (e.g.PABC123, XYZ123, GHEHEH), then index it so it can use for joining with the tblPODetail table.

I attempted to create an indexed view with the filter, but when I run the query with this view, it failed.

Alter VIEW vw_tblVendorShippingInfo WITH SCHEMABINDING AS
select dfID, substring(PONum,2, 6) AS POID
, vSKU, ShipQuantity
FROM dbo.tblVendorShippingInfo
WHERE 1 = 1
AND PONum like '%[^0-9]%'

Any recommendations would be appreciated.

Upvotes: 3

Views: 2774

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you are on the right track. You just need the right expression for like:

SELECT POID, SKU,
       (SELECT SUM(ShipQuantity) AS ShipQuantity 
        FROM tblVendorShippingInfo vsi
        WHERE substring(vsi.PONum, 2, 6) = Convert(varchar(10), pod.POID) AND
              vSKU = pod.SKU
       ) as QtyCount
FROM tblPODetail pod 
WHERE PONum like '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';

If you want to index this as an integer, then use a computed column:

alter table tblVendorShippingInfo add column POID_num as try_convert(int, substring(PONum, 2, 6);

create index idx_tblVendorShippingInfo_POID on tblVendorShippingInfo(POID_num);

try_convert() assumes SQL Server 2012+. You can do something similar with earlier versions of SQL Server.

Upvotes: 2

McMurphy
McMurphy

Reputation: 1312

If it's Oracle (and maybe others) you can specify an index on a virtual/computed by column.

Alternatively add a column and populate it once during the nightly upload.

Upvotes: 0

Related Questions