Merge Join vs. Lookup vs. Custom Script - which is fastest?

Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?Have you run your own tests?

Take your source and throw it through each of the above options and finally into a row counter. Compare the time it takes to get through the whole dataflow.|||

TheViewMaster wrote:

Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?

Only you can answer that question. test and measure test and measure, test and emasure.

-Jamie

|||Thanks guys for your answers - I will try it out performance testing this weekend on my free time.
So far it has seemed to me merge join is slower than lookup, however, lookup seems to take much longer than i like it to - so i was wondering if creating a script transform would be better solution... Just wanted to get an idea - based on you experience which option do you use?|||

TheViewMaster wrote:

Thanks guys for your answers - I will try it out performance testing this weekend on my free time.
So far it has seemed to me merge join is slower than lookup, however, lookup seems to take much longer than i like it to - so i was wondering if creating a script transform would be better solution... Just wanted to get an idea - based on you experience which option do you use?

OK. Well I am loath to give my opinions on performance comparisons but I'd lay alot of money to say that script transform will be slowest.

-Jamie

|||

If you do test the three methods, please post the results here. I am using custom script for lookups (small reference lists but millions of source rows in pipeline) but I would like to know how large reference lists perform.

|||For those posting to this thread and reading it, please watch the Webcast presented by Donald Farmer on performance and scale in SSIS. In there Donald talks about benchmarking and how to set up SSIS to obtain timings associated with different aspects of a package.

TechNet Webcast: SQL Server 2005 Integration Services: Performance and Scale (Level 400)
MS TechNet Event ID: 1032298087

I don't know if this link will work for anyone:

https://msevents.microsoft.com/CUI/Register.aspx?culture=en-US&EventID=1032298087&CountryCode=US&IsRedirect=false|||Where can I report a BUG about this forum - I have a 50/50 chance that when I try to create a hyperlink in my post - the Firefox crashes.
(Thank god I copied and pasted the following post to notepad before "doing the hyperlink trick")|||So here we go:
I'm running the tests on my workstation WinXP, 2.93GHz, 2.5gb ram.
The DB is accessed over the LAN.

Test1 (Lookup):
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Lookup is a query access table 248250 records pulling 61280 records and about 25 columns
2 outputs - Listing Found (56523 rows) and Error Listing Not found (118990 rows)
Also lookup is Full Cache mode and gives Warning: found duplicate key values.
Result:
Finished, 4:11:00 PM, Elapsed time: 00:00:15.437
Note: Memory usage of PC peaked at 1.8GB with CPU usage jumping to 100% once.

Test 2 (Merge Join):
1st Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
2nd source: OLE DB Source with query access table 248250 records pulling 61280 records and about 25 columns with ORDER BY ID. Out put is marked sorted by ID column.
1st source is Sorted using "Sort transform".
Then "Merge Joined" with ole db via Left outer join (Sort on left)
Then "Conditional Split" based on ISNULL(oledbsource.ID)
Result:
Finished, 4:49:33 PM, Elapsed time: 00:01:14.235
Note: Memory usage of PC peaked at 2.6GB with CPU usage jumping to 100% twice.

Test3 (Script Transform) -
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Script transform to do a lookup based on key column for each row in pipeline.
Result:
Cancelled after 30 minutes of processing - during which it had process 11547 records (out of 175513)
Note: Memory usage was stable around 1GB and CPU near 5% usage

My Conclusion:
Although I was concerned with the performace of lookup transform - for testing whether data to be inserted or updated - it seems thats not the culprit - the root of evil seems to be OLE DB update command and OLE DB Destination source (atm we r using SQL 2000 db - upgrading to 2005 soon).
Although Script transform consumed least amount of machine resources - executing 100K+ sql queries against db will take too long.
Although merge join Elapse time is not bad - resource usage and 3 more steps than lookup are negatives.
So i think next weekends performance testing is how to make faster INSERTs/UPDATEs to DB

Test 1 & 2 are based on Jamie Thomson article - http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Test 3 is based on Greg Van Mullem article - http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm|||

TheViewMaster wrote:

So here we go:
I'm running the tests on my workstation WinXP, 2.93GHz, 2.5gb ram.
The DB is accessed over the LAN.

Test1 (Lookup):
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Lookup is a query access table 248250 records pulling 61280 records and about 25 columns
2 outputs - Listing Found (56523 rows) and Error Listing Not found (118990 rows)
Also lookup is Full Cache mode and gives Warning: found duplicate key values.
Result:
Finished, 4:11:00 PM, Elapsed time: 00:00:15.437
Note: Memory usage of PC peaked at 1.8GB with CPU usage jumping to 100% once.

Test 2 (Merge Join):
1st Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
2nd source: OLE DB Source with query access table 248250 records pulling 61280 records and about 25 columns with ORDER BY ID. Out put is marked sorted by ID column.
1st source is Sorted using "Sort transform".
Then "Merge Joined" with ole db via Left outer join (Sort on left)
Then "Conditional Split" based on ISNULL(oledbsource.ID)
Result:
Finished, 4:49:33 PM, Elapsed time: 00:01:14.235
Note: Memory usage of PC peaked at 2.6GB with CPU usage jumping to 100% twice.

Test3 (Script Transform) -
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Script transform to do a lookup based on key column for each row in pipeline.
Result:
Cancelled after 30 minutes of processing - during which it had process 11547 records (out of 175513)
Note: Memory usage was stable around 1GB and CPU near 5% usage

My Conclusion:
Although I was concerned with the performace of lookup transform - for testing whether data to be inserted or updated - it seems thats not the culprit - the root of evil seems to be OLE DB update command and OLE DB Destination source (atm we r using SQL 2000 db - upgrading to 2005 soon).
Although Script transform consumed least amount of machine resources - executing 100K+ sql queries against db will take too long.
Although merge join Elapse time is not bad - resource usage and 3 more steps than lookup are negatives.
So i think next weekends performance testing is how to make faster INSERTs/UPDATEs to DB

Test 1 & 2 are based on Jamie Thomson article - http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Test 3 is based on Greg Van Mullem article - http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Excellent stuff. This is really valuable information. Thank you. I've updated my post with a link to here.

|||

Yes thanks for posting very interesting info. Today I am going to change all my script lookups to use stringbuilder class and methods (strongly recommended in all the .net literature where performance is important when modifyng strings). Currently all my lookup script transforms use object based .net string variables which are notoriously terrible performers when the string values are repeatedly modified. Do you know which approach your script transform used? (assuming your are creating and modifying string variables in your lookup script)...

If I detect the same low processor usage in my script lookups I may also try and partition the pipeline to get a lookup to run with multiple threads...

Ken

|||My script does a lookup something similar to as described aforementioned Van Mullem article:

Public Overrides Sub

PreExecute()

sqlCmd = New

SqlCommand("SELECT KeyCustomer, CustomerName

FROM tblCustomer WHERE(KeyCustomer = @.KeyCustomer)", sqlConn)

sqlParam = New

SqlParameter("@.KeyCustomer",

SqlDbType.Int)

sqlCmd.Parameters.Add(sqlParam)

End Sub

Public Overrides Sub

CustomerRecordsInput_ProcessInputRow(ByVal Row As CustomerRecordsInputBuffer)

Dim

reader As SqlDataReader

sqlCmd.Parameters("@.KeyCustomer").Value = Row.CUNO

reader = sqlCmd.ExecuteReader()

If

reader.Read() Then

Row.DirectRowToUpdateRecordsOutput()

Else

Row.DirectRowToInsertRecordsOutput()

End If

reader.Close()

End Sub

|||Ken - is your script performing a lookup from another source in pipeline?
<boy i'd like to know how to do that>

Also - any suggestions how to improve performance of OLE DB Update command?|||

Do a fair comparison though. Either change your query to cache the rows from SQL or disable caching on the lookup. Oranges != Apples.

A non cached lookup will be expremely slow as was your script component.

|||

Crispin wrote:

Do a fair comparison though. Either change your query to cache the rows from SQL or disable caching on the lookup. Oranges != Apples.

A non cached lookup will be expremely slow as was your script component.

It would be best to try to replicate full caching in the script component. The purpose of the exercise was to see which was faster. So, we know how fast (and legitimately so) the lookup component was, now how fast can we get the script component to process?

The question is how fast can each of the elements process their data, not how slow can we make them work.

Phil

Merge Join vs. Lookup vs. Custom Script - which is fastest?

Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?Have you run your own tests?

Take your source and throw it through each of the above options and finally into a row counter. Compare the time it takes to get through the whole dataflow.|||

TheViewMaster wrote:

Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?

Only you can answer that question. test and measure test and measure, test and emasure.

-Jamie

|||Thanks guys for your answers - I will try it out performance testing this weekend on my free time.
So far it has seemed to me merge join is slower than lookup, however, lookup seems to take much longer than i like it to - so i was wondering if creating a script transform would be better solution... Just wanted to get an idea - based on you experience which option do you use?|||

TheViewMaster wrote:

Thanks guys for your answers - I will try it out performance testing this weekend on my free time.
So far it has seemed to me merge join is slower than lookup, however, lookup seems to take much longer than i like it to - so i was wondering if creating a script transform would be better solution... Just wanted to get an idea - based on you experience which option do you use?

OK. Well I am loath to give my opinions on performance comparisons but I'd lay alot of money to say that script transform will be slowest.

-Jamie

|||

If you do test the three methods, please post the results here. I am using custom script for lookups (small reference lists but millions of source rows in pipeline) but I would like to know how large reference lists perform.

|||For those posting to this thread and reading it, please watch the Webcast presented by Donald Farmer on performance and scale in SSIS. In there Donald talks about benchmarking and how to set up SSIS to obtain timings associated with different aspects of a package.

TechNet Webcast: SQL Server 2005 Integration Services: Performance and Scale (Level 400)
MS TechNet Event ID: 1032298087

I don't know if this link will work for anyone:

https://msevents.microsoft.com/CUI/Register.aspx?culture=en-US&EventID=1032298087&CountryCode=US&IsRedirect=false|||Where can I report a BUG about this forum - I have a 50/50 chance that when I try to create a hyperlink in my post - the Firefox crashes.
(Thank god I copied and pasted the following post to notepad before "doing the hyperlink trick")|||So here we go:
I'm running the tests on my workstation WinXP, 2.93GHz, 2.5gb ram.
The DB is accessed over the LAN.

Test1 (Lookup):
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Lookup is a query access table 248250 records pulling 61280 records and about 25 columns
2 outputs - Listing Found (56523 rows) and Error Listing Not found (118990 rows)
Also lookup is Full Cache mode and gives Warning: found duplicate key values.
Result:
Finished, 4:11:00 PM, Elapsed time: 00:00:15.437
Note: Memory usage of PC peaked at 1.8GB with CPU usage jumping to 100% once.

Test 2 (Merge Join):
1st Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
2nd source: OLE DB Source with query access table 248250 records pulling 61280 records and about 25 columns with ORDER BY ID. Out put is marked sorted by ID column.
1st source is Sorted using "Sort transform".
Then "Merge Joined" with ole db via Left outer join (Sort on left)
Then "Conditional Split" based on ISNULL(oledbsource.ID)
Result:
Finished, 4:49:33 PM, Elapsed time: 00:01:14.235
Note: Memory usage of PC peaked at 2.6GB with CPU usage jumping to 100% twice.

Test3 (Script Transform) -
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Script transform to do a lookup based on key column for each row in pipeline.
Result:
Cancelled after 30 minutes of processing - during which it had process 11547 records (out of 175513)
Note: Memory usage was stable around 1GB and CPU near 5% usage

My Conclusion:
Although I was concerned with the performace of lookup transform - for testing whether data to be inserted or updated - it seems thats not the culprit - the root of evil seems to be OLE DB update command and OLE DB Destination source (atm we r using SQL 2000 db - upgrading to 2005 soon).
Although Script transform consumed least amount of machine resources - executing 100K+ sql queries against db will take too long.
Although merge join Elapse time is not bad - resource usage and 3 more steps than lookup are negatives.
So i think next weekends performance testing is how to make faster INSERTs/UPDATEs to DB

Test 1 & 2 are based on Jamie Thomson article - http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Test 3 is based on Greg Van Mullem article - http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm|||

TheViewMaster wrote:

So here we go:
I'm running the tests on my workstation WinXP, 2.93GHz, 2.5gb ram.
The DB is accessed over the LAN.

Test1 (Lookup):
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Lookup is a query access table 248250 records pulling 61280 records and about 25 columns
2 outputs - Listing Found (56523 rows) and Error Listing Not found (118990 rows)
Also lookup is Full Cache mode and gives Warning: found duplicate key values.
Result:
Finished, 4:11:00 PM, Elapsed time: 00:00:15.437
Note: Memory usage of PC peaked at 1.8GB with CPU usage jumping to 100% once.

Test 2 (Merge Join):
1st Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
2nd source: OLE DB Source with query access table 248250 records pulling 61280 records and about 25 columns with ORDER BY ID. Out put is marked sorted by ID column.
1st source is Sorted using "Sort transform".
Then "Merge Joined" with ole db via Left outer join (Sort on left)
Then "Conditional Split" based on ISNULL(oledbsource.ID)
Result:
Finished, 4:49:33 PM, Elapsed time: 00:01:14.235
Note: Memory usage of PC peaked at 2.6GB with CPU usage jumping to 100% twice.

Test3 (Script Transform) -
Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns
Script transform to do a lookup based on key column for each row in pipeline.
Result:
Cancelled after 30 minutes of processing - during which it had process 11547 records (out of 175513)
Note: Memory usage was stable around 1GB and CPU near 5% usage

My Conclusion:
Although I was concerned with the performace of lookup transform - for testing whether data to be inserted or updated - it seems thats not the culprit - the root of evil seems to be OLE DB update command and OLE DB Destination source (atm we r using SQL 2000 db - upgrading to 2005 soon).
Although Script transform consumed least amount of machine resources - executing 100K+ sql queries against db will take too long.
Although merge join Elapse time is not bad - resource usage and 3 more steps than lookup are negatives.
So i think next weekends performance testing is how to make faster INSERTs/UPDATEs to DB

Test 1 & 2 are based on Jamie Thomson article - http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Test 3 is based on Greg Van Mullem article - http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Excellent stuff. This is really valuable information. Thank you. I've updated my post with a link to here.

|||

Yes thanks for posting very interesting info. Today I am going to change all my script lookups to use stringbuilder class and methods (strongly recommended in all the .net literature where performance is important when modifyng strings). Currently all my lookup script transforms use object based .net string variables which are notoriously terrible performers when the string values are repeatedly modified. Do you know which approach your script transform used? (assuming your are creating and modifying string variables in your lookup script)...

If I detect the same low processor usage in my script lookups I may also try and partition the pipeline to get a lookup to run with multiple threads...

Ken

|||My script does a lookup something similar to as described aforementioned Van Mullem article:

Public Overrides Sub PreExecute()

sqlCmd = New SqlCommand("SELECT KeyCustomer, CustomerName FROM tblCustomer WHERE(KeyCustomer = @.KeyCustomer)", sqlConn)

sqlParam = New SqlParameter("@.KeyCustomer", SqlDbType.Int)

sqlCmd.Parameters.Add(sqlParam)

End Sub

Public Overrides Sub CustomerRecordsInput_ProcessInputRow(ByVal Row As CustomerRecordsInputBuffer)

Dim reader As SqlDataReader

sqlCmd.Parameters("@.KeyCustomer").Value = Row.CUNO

reader = sqlCmd.ExecuteReader()

If reader.Read() Then

Row.DirectRowToUpdateRecordsOutput()

Else

Row.DirectRowToInsertRecordsOutput()

End If

reader.Close()

End Sub

|||Ken - is your script performing a lookup from another source in pipeline?
<boy i'd like to know how to do that>

Also - any suggestions how to improve performance of OLE DB Update command?|||

Do a fair comparison though. Either change your query to cache the rows from SQL or disable caching on the lookup. Oranges != Apples.

A non cached lookup will be expremely slow as was your script component.

|||

Crispin wrote:

Do a fair comparison though. Either change your query to cache the rows from SQL or disable caching on the lookup. Oranges != Apples.

A non cached lookup will be expremely slow as was your script component.

It would be best to try to replicate full caching in the script component. The purpose of the exercise was to see which was faster. So, we know how fast (and legitimately so) the lookup component was, now how fast can we get the script component to process?

The question is how fast can each of the elements process their data, not how slow can we make them work.

Phil

Merge Join (Full outer join) never finishes.

I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?

Try this - replace your destination with a RowCount component - it does nothing except count rows into a variable. if the package succeeds, then you have narrowed the problem down to an issue with your destination.

If there is an issue with your destination, you should start to look into disk and memory usage.

hth - I'll look forward to hearing more.

Donald

|||The way I worked around the problem was to separate it into 2 dataflows, the first one just taking the left branch of the input to the merge join and landing it to a raw destination. Then in the second data flow (the merge join) I simply used the raw file as the left input and it worked fine. Does that eliminate the possibility of it being an issue with the destination? If not then I will recreate the original scenario and replace the destination with a RowCount component as you suggested. Thanks.|||

That's a tricky one. I wonder if the combined memory usage of your script component and the merge join was stressing the box.

Donald

Merge Join

Hello all,

I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.

In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?

Thank you.

As long as you know the input is sorted, you can use the issorted property.

If it turns out not to be sorted, you could miss joins.

BobP

|||

Thank you for the reply...

So here in my case I am using a left join (in merge join)....how would I know if the raw file output from first package i sorted or not?

There is no problem to sort it again but really takes a lot of time(as the size is too big).

|||

If you are sorting it and then writing it out, it should stay sorted.

BobP

|||Ok....if it stays sorted then OK.......many Thanks

Merge Agent fails to connect to mdb

Hi all,
Here is a challenge - and I'm loosing ...
I'm trying to get a push subscription to work to an Access mdb file from
SQLServer.
I get the following message when running the Merge Agent:
'S:\network\share\access.mdb' is not a valid path. Make sure that the path
name is spelled correctly and that you are connected to the server on which
the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
I'm successfully using the subscription's Linked Server definition via QA.
So I conclude that the .mdb path is valid.
I've successfully test the push replication with the .mdb file as a local
file.
My guess is that it is a security context issue for the Merge Agent -
however I don't know how to correct it!!!
I've checked the Agent definition - and it is running with the owner set to
the local admin account.
Both my SQLServer Instance and Agent service use the same local
administrator account (not LocalSystem). This account has got access to the
share and can open the mdb file via Access).
Environment:
SQLServer PE V7.0 with SP3a
Windows 2003 Server with SP1
Hope that someone has been here, and got the t-shirt...
Peter
Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
everytime the agent runs it creates a new copy of the database,
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
> Hi all,
> Here is a challenge - and I'm loosing ...
> I'm trying to get a push subscription to work to an Access mdb file from
> SQLServer.
> I get the following message when running the Merge Agent:
> 'S:\network\share\access.mdb' is not a valid path. Make sure that the
> path
> name is spelled correctly and that you are connected to the server on
> which
> the file resides.
> (Source: MS.Jet.4.0 (Agent); Error number: -1023)
> I'm successfully using the subscription's Linked Server definition via QA.
> So I conclude that the .mdb path is valid.
> I've successfully test the push replication with the .mdb file as a local
> file.
> My guess is that it is a security context issue for the Merge Agent -
> however I don't know how to correct it!!!
> I've checked the Agent definition - and it is running with the owner set
> to
> the local admin account.
> Both my SQLServer Instance and Agent service use the same local
> administrator account (not LocalSystem). This account has got access to
> the
> share and can open the mdb file via Access).
> Environment:
> SQLServer PE V7.0 with SP3a
> Windows 2003 Server with SP1
> Hope that someone has been here, and got the t-shirt...
> --
> Peter
|||Hi Hilary,
Yes S is a mapped network drive.
I have created a new Linked Server def using the UNC reference to the mdb.
Again the linked server def works fine when used in Query Analyser to select
data from the mdb.
However, I again get the msg below in my Merge Agent when using the Linked
Server def in a push subscription:
'\\server\path1\path2\access.mdb' is not a valid path. Make sure that the
path name is spelled correctly and that you are connected to the server on
which the file resides.
(Source: MS.Jet.4.0 (Agent); Error number: -1023)
------
Any ideas?
Curious about your second comment about creating a new copy of the database
everytime the agent runs - I did not see this when running the agent against
my local test copy of the mdb.
Regards,
Peter
Peter
"Hilary Cotter" wrote:

> Is S a network drive? If so you have to access it as a UNC. Secondly, IIRC
> everytime the agent runs it creates a new copy of the database,
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:D872C520-1D22-4397-8A55-7E0A02DA4D26@.microsoft.com...
>
>

Merge a PDF File and SQL Reporting Services Output

Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?

thanks

You could print the pdf to an image writer and use the images as background images in your Reporting Services report.

Or if the Reporting Services output is mainly data you could look at using the output data as input into you thrid party application (or vise versa).

hth

-- Leah

|||

I've tried what Leah suggested and the creation of the gif file was identical to the pdf (same width and height). However, when I used it as a background in the VS2005 report designer, the image became larger. How do you maintain the same dimensions of the original image when it is used as a background image?

Thanks

|||I came across a post from a different forum that was the closest to solving this issue. The solution was to scan a document at 96dpi prior to using it as the background. It wasn't perfect but it got me into the ball park where I could adjust positioning of the controls on a case by case basis.

Merge a PDF File and SQL Reporting Services Output

Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?

thanks

You could print the pdf to an image writer and use the images as background images in your Reporting Services report.

Or if the Reporting Services output is mainly data you could look at using the output data as input into you thrid party application (or vise versa).

hth

-- Leah

|||

I've tried what Leah suggested and the creation of the gif file was identical to the pdf (same width and height). However, when I used it as a background in the VS2005 report designer, the image became larger. How do you maintain the same dimensions of the original image when it is used as a background image?

Thanks

|||I came across a post from a different forum that was the closest to solving this issue. The solution was to scan a document at 96dpi prior to using it as the background. It wasn't perfect but it got me into the ball park where I could adjust positioning of the controls on a case by case basis.

Merge 2 Databases?

My problem is this:

I have a database file on a hard disk. I have to read that data and merge it into a database on a sql server. How can you merge ? The databases are set up the exact same way. The only difference in them is the data.

Also, in the database file I have image names, I have to compare them to what is on the database on the sql server.

Thanks

There are many options.
bcp out and in|||

Not very much data at all. Maybe 500 rows with 10 columns.

I am worried about using DTS because what happens with the foreign keys and primary keys?

Say I have imageID as a primary...and I have imageID 1, 2, 3 in both databases....won't there be conflicts when I run the DTS?

|||

In that case I would go for the good old T-SQL syntax.

You can work cross database.

INSERT INTO myTable (myField, myOtherField)
SELECT myField, myOtherField FROM myOtherDatabase.dbo.myTable

|||

Thanks Wesley for that...

But lets say I have imageID 1, 2, 3 in DB1 and I have imageID 1,2,3 in DB2. Now the images associated to those ID's are totally different. How can I insert the data from DB2 into DB1 without getting errors? I have to somehow increment the ID's in DB2 so they are 3, 4, 5?

|||Takes some logic, you could use a cursor since it's a small recordset and insert the records one by one using SCOPE_IDENTITY to determine the current ID for the child records.

memory performance

The server is a SQL2k standard edition SP3, on Win2k server.
I have 4GB physical memory and 5GB page file.
I know that SQL2k standard edition can only utilize 2GB memory.
Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
The working set maximum is 1779MB and pagefults/sec maximum is 34.
Do I have memory problem?
Thanks in advance.
Looks fine to me.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:BC14859F-F926-484A-9DFD-569C301A43E6@.microsoft.com...
> The server is a SQL2k standard edition SP3, on Win2k server.
> I have 4GB physical memory and 5GB page file.
> I know that SQL2k standard edition can only utilize 2GB memory.
> Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
> The working set maximum is 1779MB and pagefults/sec maximum is 34.
> Do I have memory problem?
> Thanks in advance.

memory performance

The server is a SQL2k standard edition SP3, on Win2k server.
I have 4GB physical memory and 5GB page file.
I know that SQL2k standard edition can only utilize 2GB memory.
Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
The working set maximum is 1779MB and pagefults/sec maximum is 34.
Do I have memory problem?
Thanks in advance.Looks fine to me.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:BC14859F-F926-484A-9DFD-569C301A43E6@.microsoft.com...
> The server is a SQL2k standard edition SP3, on Win2k server.
> I have 4GB physical memory and 5GB page file.
> I know that SQL2k standard edition can only utilize 2GB memory.
> Running perfmon caught Process\SQLserver\pagefaults/sec and working set.
> The working set maximum is 1779MB and pagefults/sec maximum is 34.
> Do I have memory problem?
> Thanks in advance.