topic Re: Performance Improvement while extracting data from SQL Server in QlikView

Performance Improvement while extracting data from SQL Server

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

There are two tables 1. Dimension - Product 2. Fact - TableX, it has 10 years data.

Requirement - want to extract only Yesterday data. This query take much time, how can i improve performance here (QlikView side).

And please let me know how can i improve performance at SQL side as well if possible?

Thanks,

Nihhal.

Re: Performance Improvement while extracting data from SQL Server

Anil_Babu_Samineni — Mon, 06 Mar 2017 06:41:02 GMT

Which Query you have written in Qlik?

Re: Performance Improvement while extracting data from SQL Server

muthukumar77 — Mon, 06 Mar 2017 09:31:41 GMT

Hi,

You have to retrive only yesterday data in SQL side. I mean put where condition in SQL query. like Date="Yesterday Date".

Re: Performance Improvement while extracting data from SQL Server

Miguel_Angel_Baeyens — Mon, 06 Mar 2017 10:14:12 GMT

Start by placing a WHERE clause in your SQL query to retrieve only rows corresponding to that date.
Pull only fields you are going to use, do not do SELECT *
If there is none, think of creating an index on that Date field in the database.

Re: Performance Improvement while extracting data from SQL Server

nihhalmca — Mon, 06 Mar 2017 11:32:08 GMT

Hi Anil babu,

We write query in edit script while extracting right that's what i am talking like:

SQL Select

----

From table where date ='yesterday';

MY question is what are the optimize techniques to improve performance here.

Re: Performance Improvement while extracting data from SQL Server

nihhalmca — Mon, 06 Mar 2017 11:33:30 GMT

Hi Muthukumar,

I am fine with writing where condition however my question is what are the optimize techniques to improve performance.

Re: Performance Improvement while extracting data from SQL Server

nihhalmca — Mon, 06 Mar 2017 11:34:55 GMT

Hi Miguel,

Point 1 and 2 as usual however i have to think about 3 point.

Thanks.

Re: Performance Improvement while extracting data from SQL Server

Anil_Babu_Samineni — Mon, 06 Mar 2017 11:57:24 GMT

1) Create variable for yesterday and then call that variable into script

2) Improve - Will happen if you would go for qvd and then call that qvd with same Where condition by help of variable

3) Migue already noted good point (3rd one)

Re: Performance Improvement while extracting data from SQL Server

Colin-Albert — Mon, 06 Mar 2017 12:42:44 GMT

Have a look at the techniques for Incremental Load in your script.

Incremental loading - All scenarios

Incremental Load in Qlikview - Sources

Re: Performance Improvement while extracting data from SQL Server

jagan — Wed, 17 May 2017 07:23:19 GMT

Hi Nihhal,

Following this for improving the performance

- Load only the required columns

- Load only the required data, for example if you just need 5 years of data just load that with where

- Load less data as much as possible, check with your user and apply filters accordingly

- Implement Incremental loading, so that it loads only latest data and from database and this reduce the load time.

- Check in database whether the statistics and indexes are updating periodically

Hope this helps you.

Regards,

Jagan.

Re: Performance Improvement while extracting data from SQL Server

Anders_Eriksson — Wed, 17 May 2017 07:40:36 GMT

Crucial for performance is having an index on the Date field in the SQL Server table.