SQL QUERY IN EXTRACTOR

[CVR]

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 

[marcus_sommer]

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

[CVR]

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 ?

[marcus_sommer]

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

[CVR]

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

[marcus_sommer]

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