A sql query where results need to factor in a comma delimited field.

[darkgen]

OK so I have 2 tables:

Table 1:
tbl_Datastore
Datastore_Name | ESX_Hosts
DS1 | ESX_1,ESX_2
DS2 | ESX_2,ESX_3

Table 2:
tbl_ESXHost
ESX_Host | Another_Field
ESX_1 | foo1
ESX_2 | foo2
ESX_3 | foo3

I want to run a query which will result in a row of data for each ESX_Host per each individual Datastore that is presented:

The following query works but its horrendously slow!

Select tbl_ESXHost.ESX_Host, tbl_ESXHost.Another_Field, tbl_Datastore.Datastore_Name FROM tbl_Datastore CROSS JOIN tbl_ESXHost WHERE (tbl_Datastore.ESX_Hosts like '%' + tbl_ESXHost.ESX_Host + '%')

Help! Thanks in advance!

 

[darkgen]

tbl_Datastore contains probably a thousand rows of data and tbl_ESXHost less than 200, but tbl_Datastore.ESX_Hosts can contain up to 10 ESX hosts. However even with these low numbers its pitiful. I originally wanted to run the query hourly along with several others but in all honesty I could probably get away with running it once a day and yes there are other columns of data contained on both tables I would want.

Question is with creating a table with that link and insert from each, how do you still reference the comma delimited field? The data within the tables is generated using RVTools, which I do not have any direct control over.

 

[darkgen]

Apologies was slightly off with the figures:
tbl1 - ~3000 rows
tbl2 -~1000 rows
The query in its current format generates ~12000 returned rows and it takes 10s of seconds to complete.
Nothing confidential here so :

Here's the full query:
SELECT tbl_ESXHosts.Host, tbl_ESXHosts.Model,
tbl_Datastore.Name, tbl_Datastore.CapacityMB,
tbl_Datastore.FreeMB, tbl_Datastore.FreePERCENT,
tbl_ESXHosts.Datacenter, tbl_ESXHosts.[#Memory] / 1024 AS RAM,
tbl_ESXHosts.VirtualCentre, tbl_ESXHosts.LastWriteTime,
tbl_ESXHosts.ESXVersion
FROM tbl_Datastore CROSS JOIN
tbl_ESXHosts
WHERE (tbl_Datastore.Hosts LIKE '%' + tbl_ESXHosts.Host + '%')

And links to the tables(csv):
http://mire.darkgen.net/~paul/tmp/

Gawping through the code in the interim.

Thanks, Paul.

 

[darkgen]

Wow, understood re indexing. Many thanks for this, much appreciated and it will definitely assist in my approach in future. I'll pour over this in the morning and get back to you.

Thanks again, Paul.

 

[darkgen]

I couldn't wait and played and I think I'm geting there, bear with me

So I've included these few lines to check and delete whether the temp tables exist:

IF (SELECT object_id('numbers')) IS NOT NULL
BEGIN
DROP TABLE numbers
END

IF (SELECT object_id(tbl_vDatastoreHostsAndInfo')) IS NOT NULL
BEGIN
DROP TABLE tbl_vDatastoreHostsAndInfo
END

However, I'm getting multiple duplicated returned rows(13005) and the query is again running into 10s of seconds. Dropping DISTINCT into both queries will return just 1219 rows (less than a second) but only returns a single row for each host.

 

[darkgen]

Something is going awry - very odd.

But I think I'll have to call it a night... Appreciate your help and support.

Below is the code (with slightly changed identifers).

IF (SELECT object_id('numbers')) IS NOT NULL
BEGIN
    DROP TABLE numbers
END

IF (SELECT object_id('KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo')) IS NOT NULL
BEGIN
    DROP TABLE KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo
END

IF (SELECT object_id('numbers')) IS NOT NULL
BEGIN
    DROP TABLE numbers
END

IF (SELECT object_id('KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo')) IS NOT NULL
BEGIN
    DROP TABLE KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo
END

--Numbers Table, assuming < 10,000 elements of delimited string 
SELECT TOP 10000 IDENTITY(int,1,1) AS id
    INTO numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
	CROSS JOIN sys.objects s3
ALTER TABLE numbers ADD CONSTRAINT PK_numbersTEST PRIMARY KEY CLUSTERED (id) WITH FILLFACTOR=100

--get the fields you want from tbl_Datastore, also parse the hosts so 1 row per host
SELECT dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.Name, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.Address, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.CapacityMB, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.FreeMB, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.FreePERCENT, 
 LTRIM(RTRIM(SUBSTRING(Hosts, id, CHARINDEX(',', Hosts + ',', id) - id))) as Host
INTO dbo.KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo
FROM numbers, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore
WHERE id <= CONVERT(INT, LEN(Hosts))
AND SUBSTRING(',' + Hosts, id, 1) = ','

--final select joins to tbl_ESXHosts to get the required fields, and uses table created above to get those for the data store. 
SELECT dbo.KOPSAWN00244_USPRD1448_RVT_vHost.Host, dbo.KOPSAWN00244_USPRD1448_RVT_vHost.Model, 
dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.Name, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.CapacityMB, 
dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.FreeMB, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.FreePERCENT, 
dbo.KOPSAWN00244_USPRD1448_RVT_vHost.Datacenter, dbo.KOPSAWN00244_USPRD1448_RVT_vHost.[#Memory] / 1024 AS RAM, 
dbo.KOPSAWN00244_USPRD1448_RVT_vHost.VirtualCentre, dbo.KOPSAWN00244_USPRD1448_RVT_vHost.LastWriteTime, 
dbo.KOPSAWN00244_USPRD1448_RVT_vHost.ESXVersion
FROM dbo.KOPSAWN00244_USPRD1448_RVT_vDatastoreHostsAndInfo, dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore, dbo.KOPSAWN00244_USPRD1448_RVT_vHost
WHERE dbo.KOPSAWN00244_USPRD1448_RVT_vDatastore.Hosts = dbo.KOPSAWN00244_USPRD1448_RVT_vHost.Host

 

[darkgen]

Ahhh, yes. What a wally.

Many thanks, its now returning expected results and in less than a second. Brilliant. Really appreciate your assistancew on this.

Cheers, Paul.