
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SQL QUERY IN EXTRACTOR
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
