Performance Improvement while extracting data from SQL Server

[nihhalmca]

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.

[Anil_Babu_Samineni]

Which Query you have written in Qlik?

[muthukumar77]

Hi,

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

[Miguel_Angel_Baeyens]

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

[nihhalmca]

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.

[nihhalmca]

Hi Muthukumar,

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

[nihhalmca]

Hi Miguel,

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

Thanks.

[Anil_Babu_Samineni]

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)

[Colin-Albert]

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

Incremental loading - All scenarios

Incremental Load in Qlikview - Sources

[jagan]

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.