Best way to load data

[#1]

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?

[maxgro]

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?

[#3]

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

[maxgro]

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?

[#5]

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

[simonb2013]

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...

[oknotsen]

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.

[maxgro]

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

db.dbo.tablename

or

db..tablename

[#9]

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

[luciancotea]

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