query is running long time

[ll2016]

Hi Devart,

I am trying to run a query directly from the devart add in but its taking forever. I even try with just simple join with couple fields from 2 tables as a start but it just keep going. Currently, I import all the data onto the spreadsheet and use a different tool to query the data but then each time i need to refresh data onto the spreadsheet and its a hassle but it works (using the same query as below).

So, just wondering is it possible to query directly to the table through dev art add in without importing the data onto a spreadsheet with a much faster speed? This is what iam trying to run (Currently it just keep going for long time and I manually kill the query):

SELECT t1."Local Store", t1.totalnumofmembers, t2.totalspendforall, t3.signupmembers, t4.nospendmembers, t5.lastmthspendmembers
FROM
(
SELECT "Local Store", COUNT(*) AS totalnumofmembers
FROM ListMembers
WHERE "Member Card Number" IS NOT NULL and ListId = '9753346d50'
GROUP BY "Local Store"
) t1
INNER JOIN
(
SELECT "Local Store", SUM("total spend") AS totalspendforall
FROM ListMembers where ListId = '9753346d50'
GROUP BY "Local Store"
) t2
ON t1."Local Store" = t2."Local Store"
INNER JOIN
(SELECT "Local Store", COUNT(*) AS signupmembers
FROM ListMembers where createddate >= to_date('2016-05-01','YYYY-MM-DD') and "Member Card Number" IS NOT NULL and ListId = '9753346d50'
GROUP BY "Local Store") t3
ON t2."Local Store" = t3."Local Store"
INNER JOIN
(SELECT "Local Store", COUNT(*) AS nospendmembers
FROM ListMembers where "Total Spend" =0 and "Member Card Number" IS NOT NULL and ListId = '9753346d50'
GROUP BY "Local Store") t4
ON t3."Local Store" = t4."Local Store"
INNER JOIN
(SELECT "Local Store", COUNT(*) AS lastmthspendmembers
FROM ListMembers
where "Member Card Number" is not null
and ListId = '9753346d50'
and "Total Spend" != 0
and "Last Purchase Date" BETWEEN datetime('now', '-28 days') AND datetime('now', 'localtime')
GROUP BY "Local Store") t5
ON t4."Local Store" = t5."Local Store"


Thank you, Dev Art

[Pinturiccio]

We could not reproduce the issue. Please tell us the result of the following query:

Code: Select all

SELECT COUNT(*) FROM ListMembers

Please also tell us how many merge tags you have in total for all of the Lists.

[ll2016]

Hi Pinturiccio,

The returned rows is '463973'.

I have about 4 merge tags to be in my select statement. Does that mean when the processing is done it will run through all 400k+ for each merge tag?

thank you so much again for the prompt reply

[Pinturiccio]

The returned rows is '463973'

For such a large number of rows it is OK to take a lot of time to load. Besides reading merge tags takes additional time.