What hits the database more, Loading full table once, or restricted 3 times

[#1]

I do not have much background in understanding performance when loading from the database.

I currently load 1 table 3 times, but it is restricted for each time with conditions. (The conditions make it so the same data is never loaded more than once.)

I was wondering if it would improve performance by loading the full table once, then resident loading the table the three times with the conditions, then dropping the full table.

Thoughts?

Also note that I am note using all the data in the table with the 3 hits I currently use.

[Anonymous]

Hi Marc,

I always though that loading same table more then once may be a issue for loading and so I used to concatenate them with conditions on database. Which did not solve my issue as my qvws use to take time to load.

What I then found is that qvw renders each row to fetch data. Which in return usually take time then joins. Hence, you should make logical/ physical join in the qvw that would simplify the data and would be irrespective of how many times you load the same table with different conditions. (Assume you might have some logical joins for instance time dimesnion, products, etc..) Create structured data model.

Keeping in consideration which should also simplify your expressions for presentation purpose.

[jonathandienst]

Hi

As you are not using all the data, I would recommend that you only run the select with the conditions, but I would also avoid making 3 round trips to the server. Rather combine the expressions into a single SQL select. In the code fragments below, replace Condition1 - Condition 3 with your conditions.

I would also suggest marking the records with a group indicator. You  say the conditions are mutually exclusive, so you could use a case statement to indicate to which condition each records belongs. This syntax is for SQL Server. Other DBMS use different syntax - this part is optional, though.

Temp1:

SQL Select ...

     ...

     Case When Condition1 Then 1

          When Condition2 Then 2

          When Condition3 Then 3

          Else 0 End As Group,

     ...

From dbo.Table ...

Where (Condition1) OR (Condition2) OR (Condition3);

Result1:

NoConcatenate

LOAD * Resident Temp1

Where Group = 1;

Result2:

...

...

DROP Table Temp1;

Hope that helps

Jonathan

[flipside]

Hi Marc,

The answer to your question largely depends on how the data is stored in the source database and whether there are other connections being made to the source tables. 

If you are specifying WHERE clauses, and the data is ordered on the disk in such a way that the records are scattered throughout the table then you will get a slower return of records than if the records are all together in the same region of the table.  Furthermore if other users are accessing at the same time, then the problem could be much worse.

If you are reading a large volume of records, then you may be best to separate out the required data using the ETL tools in the database system into a table of its own and read from that without any conditions. If you have a database developer or database administrator to advise you, that would help.

flipside

[Lee_Matthews]

Hi Marc

If the 3 chunks of data combined represent the majority of data in the table then extracting the entire table is probably better. Otherwise using a where clause is best, and Jonathan's suggestion above is also going to further reduce load on the database as only one query gets executed. But you also want to ensure that there are indexes on the table that will be used by the where clause to improve data access. I assume you are talking about the Epicor DB - do you use Progress DB or SQL Server? If the latter you can run the SQL query through query analyser to identify the execution plan that SQL will use, including any indexes it will use.

[flipside]

It should be pointed out that WHERE clauses can be a real performance killer in databases and some DBAs frown on casual use of them. 

If the data is structured correctly (using a clustered index) and the WHERE clause only specifies the clustered index, then making three separate queries may actually be faster than one single query.  Other things to avoid are "SELECT *" and "ORDER BY" statements.

flipside

[#7]

Thank you everyone for your input. I just converted all of our documents to use QVD's and I ended up going around this issue by storing the entire table in a previously loaded document that needed the entire table. I will keep these ideas in mind if I come across this situation again.