topic Re: Best way to load data in QlikView

Best way to load data

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have two databases in sqlserver that have same tables with same column names and structure. One of them has data prior 90 days from today and the other has data from last 90 days. I have a sql query that runs against both databases to fetch data for a report for the past 2 years and it performs an inner join and both the tables have millions of rows.

With a single OLEDB connection I wont be able to run that query in Qlikview if i need to generate a report for the past 2 years.

I am trying to figure out what is best way to run the query from performance perspective?

Re: Best way to load data

maxgro — Fri, 11 Mar 2016 19:47:32 GMT

I have a sql query that runs against both databases to fetch data for a report for the past 2 years and it performs an inner join and both the tables have millions of rows.

could you post the query?

Re: Best way to load data

Fri, 11 Mar 2016 21:13:01 GMT

select a.AccountNumber,COUNT(distinct(rp.impid))

from DB1..accounts a inner join DB1..accountbooks at on a.accountid=at.accountid inner DB1.dbo.reportident rp

on rp.ntid = AT.ntid

where accountdate between @date1 and @date2

and rp.impid not in (select impid from DB2.dbo.TextPpl where jobid =

(select textid from DB1..Alljobs where jobid = rp.jobid)

union select personid from DB3.dbo.TextPpl where jobid = (select jobid from DB1..Alljobs where jobid = rp.jobid))

and rp.impid in (select personid from DB2.dbo.CallPpl where jobid =

(select jobid from DB1..Alljobs where jobid = rp.jobid) union select pid from DB3.dbo.CallPpl where jobid =

(select jobid from DB1..Alljobs where jobid = rp.jobid))

and rp.impid in (select impid from DB1..impident where datedat>@date1 )

group by a.accoutnumber order by a.accountnumber

DB2 and DB3 has same callppl table. But I think 3 OLEDB connections DB1,DB2 and DB3

Re: Best way to load data

maxgro — Fri, 11 Mar 2016 23:30:15 GMT

Why 3 oledb connection if the database are all on the same sqlserver?

Did you try with 1 oledb conn and the query you posted?

Re: Best way to load data

Sat, 12 Mar 2016 00:46:12 GMT

‌when I create an oledb connection it asks for database name. can the connection be changed to entire instance instead of single database

Re: Best way to load data

simonb2013 — Sat, 12 Mar 2016 09:26:09 GMT

I can't tell you the best approach from a QV point of view, but ...

if all databases are on the same server, then you can simply create a union view on one of the DBs.

Then connect to this view from QV

use DB1

GO

create view dbo.[myQVSource]

as

select F1,F2,F3 from DB1.dbo.[myTable]

union all

select F1,F2,F3 from DB2.dbo.[myTable]

etc...

Re: Best way to load data

oknotsen — Sat, 12 Mar 2016 09:59:34 GMT

Instead of wanting to solve this in your source system (your database), do this in Qlik.

Just get all the tables into Qlik, do your joining and concatenating there.

This probably also reduces the amount of work your source system has to do, so it can keep those resources for doing what you got it for in the first place.

Re: Best way to load data

maxgro — Sat, 12 Mar 2016 10:20:49 GMT

if you prefix the table with the db maybe it works, that's the reason I asked to try

db.dbo.tablename

db..tablename

Re: Best way to load data

Mon, 14 Mar 2016 11:14:16 GMT

I tried using the database name.schema name.table name and it wont create the connection.

Re: Best way to load data

luciancotea — Mon, 14 Mar 2016 14:52:35 GMT

It doesn't matter on which DB your connection is. That is the IMPLICIT database, which means it will be used if you don't provide a DB in your SQL.

So, as long as you specify the DB in your SQL, use a single copnnection.

SELECT a.* FROM DB1.dbo.Table1 as a

INNER JOIN DB2.dbo.Table2 as b

ON a.ID = b.ID

Re: Best way to load data

simonb2013 — Wed, 16 Mar 2016 08:56:09 GMT

Sasi,

Give us a better picture of what you are doing.

Are you working in Sql Server Management Studio ?

Give us the exact code you are trying.

Give us the exact error messages you are receiving.

Screen shots if possible.