topic Re: SQL QUERY IN EXTRACTOR in QlikView

SQL QUERY IN EXTRACTOR

CVR — Fri, 21 May 2021 17:45:13 GMT

Hello QlikGeeks,

I have been using the below query in the QlikView extractor to pull the data from SQL

OLD QUERY

SuperMarket:
LOAD
RowID,
OrderID,
OrderDate,
S,
T,
X,
Y,
Z;
SQL SELECT *
FROM "Master".dbo."SuperMarket"

WHERE not(S='0' and T='0' and X='0' and Y='0' and Z='0' ) and (OrderDate) <= getDate();

But our SQL team asked to me tune the query because it is occupying more disk space while executing. So I had to tune as below

NEW QUERY

SuperMarket:
LOAD
RowID,
OrderID,
OrderDate,
S,
T,
X,
Y,
Z;
SQL SELECT *
FROM "Master".dbo."SuperMarket"

WITH(NOLOCK) WHERE not(S<>0 and T<>0 and X<>0 and Y<>0 and Z<>0 ) and (OrderDate) <= getDate();

Somehow after the new query execution data is missing for few sheets. May I know if i am missing something or is any other way to tune the OLD QUERY?

Thanks in Advance,

CVR

@sunny_talwar @marcus_sommer @jagan @swuehl

Re: SQL QUERY IN EXTRACTOR

marcus_sommer — Sat, 22 May 2021 06:35:47 GMT

There are several noticeable points:

pulling only the needed fields from the database and not all fields per * wildcard
there is a field RowID which indicates that it is just a record-counter from database-table and not a real value - if so, then remove it because it will determine the biggest pointer-value and you won't need it within a regular reporting (during the development of an application it might be helpful to check and compare data on a record-level if there are any issues with missing/wrong data - but not afterwards)
both conditions are in general not the same whereby it depends from the used driver and database how the statements are interpreted - Field = '0' <> Field = 0 <> Field <> 0 because the data-type and the formatting and the evaluation-rules from the tools may have an impact
if I assume that the fields are numeric probably boolean values it should be faster to evaluate them like:
S*T*X*Y*Z>0
further you should consider to apply an incremental approach for not querying all data again and again else just pulling the new/changed records

- Marcus

Re: SQL QUERY IN EXTRACTOR

CVR — Mon, 31 May 2021 16:33:19 GMT

Sorry for the late response. Thanks for your suggestions. I made the suggested changes but it is not working for me. May I know if there is any other way ?

Re: SQL QUERY IN EXTRACTOR

marcus_sommer — Mon, 31 May 2021 17:01:51 GMT

What didn't work? Did you do a systematically testing? Maybe also with a further reduced dataset like lesser columns and/or another conditions like querying just a few days to speed up the execution times to be able test the logics and syntax in rather short times before applying it on the whole dataset.

- Marcus

Re: SQL QUERY IN EXTRACTOR

CVR — Mon, 31 May 2021 17:05:08 GMT

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Re: SQL QUERY IN EXTRACTOR

marcus_sommer — Mon, 31 May 2021 17:19:31 GMT

How many RAM consumed this app? Especially these query? After which time/ records and/or RAM consumption comes this error?

Which other tasks/processes are running in parallel? How many RAM is in general available?

Such issues needs a carefully monitoring of the vital hardware KPI's. I assume that your task may running without such errors but it's quite likely that the run-times aren't satisfying without applying any incremental approaches - therefore I suggest to consider them.

- Marcus