Merge join more than 2 tables

I need to take certain items of data from four different tables and out them into one table.

Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.

I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.

That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?

I cannot answer your direct question about the merge join, but wanted to make one comment. It is possible to recast a Left Join query as a Union and this might be a viable approach for your problem. The general form is to make a Union of the simple Join and a Not IN query.

MERGE

Hi all,
What would be SQL Server's version of this Oracle code:
****************************************
*********************
MERGE INTO destination_table dest
USING (SELECT col1, col2, col3 FROM source_table) source
ON (dest.col1 = source.col1)
WHEN MATCHED THEN
UPDATE SET dest.col2 = source.col2,
dest.col3 = source.col3
WHEN NOT MATCHED THEN
INSERT (dest.col1, dest.col2, dest.col3)
VALUES (source.col1, source.col2, source.col3)
****************************************
**************************
Thanks in advance
GoranGoran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
> What would be SQL Server's version of this Oracle code:
> ****************************************
*********************
> MERGE INTO destination_table dest
> USING (SELECT col1, col2, col3 FROM source_table) source
> ON (dest.col1 = source.col1)
> WHEN MATCHED THEN
> UPDATE SET dest.col2 = source.col2,
> dest.col3 = source.col3
> WHEN NOT MATCHED THEN
> INSERT (dest.col1, dest.col2, dest.col3)
> VALUES (source.col1, source.col2, source.col3)
> ****************************************
**************************
Alas, MERGE is implemented in SQL Server, so you will need to do:
UPDATE ...
INSERT ...
SELECT ...
WHERE NOT EXISTS
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DC4CB7551Yazorman@.127.0.0.1...
> Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
>
> Alas, MERGE is implemented in SQL Server, so you will need to do:
> UPDATE ...
> INSERT ...
> SELECT ...
> WHERE NOT EXISTS
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Memory usage of SQL Server 2005 Mobile Edition

Hi forum readers,

we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.

We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?

Does anyone have a deeper insight into that server an can answer my questions.

Best regards,
Tobias

No, we don't load entire 5MB database into program memory. Only required pages are loaded to the buffer pool as and when required.

When database is loaded, lot of runtime structures are maintained for multi proc/thread synchronization. Some of these are maintained in shared memory. This total shared memory size figure is dependent mainly on the configurable parameters buffer pool size, max database size etc. If you increase them the figure goes up.

Assuming buffer pool size is the default (160 pages ~ 160*4096), we can roughly estimate the total size to be 800 KB + (max database size/1024) KB.

For ex: for a 60MB max size database, it would roughly 860 KB.

for a 2GB max size database, it would be roughly 2800 KB

Apart from this there would be a lot of other objects that are created & maintained as the client requests. For ex rowsets/cursors. It will be too difficult to estimate these as these are created based on the client code/usage.

Thanks - Raja

|||

Hi Raja,

Thank you very much, this gives us a better understanding of the required memory and the loading process.

Best Regards,

Tobias

Memory question

Hello.

I have just intstalled 32-bit 2005 Standard Edition on a W2003 SP1 Server.

Am I right in assuming that this version will use as much memory as it can grab from the server up to a limit of 4Gb without me having to alter any settings in SQLServer or Windows?

Thanks

What's the w2003 version? If it is x86 OS, the max vas sql can use is 2GB according to default sql configration. If it is x64 OS, the max memory of sql is up to 4GB (assume the physical memory is enough)|||

apologies, it is 32-bit x86.

The physical memory in the server is 4Gb, can I make SQL use more of the available memory than 2Gb?

|||Yes, you can try to use /3GB or AWE. The actually memory usage will be increased to 2.6-2.7GB in 4G physical memory system.|||

ah-ha, I remember reading somthing about 3GB and AWE a long time ago.

The /3Gb thing increase the memory for any application but AWE increses it for SQL only, am I right?

oh, and Thanks

|||All AWE-aware applicaiton can benefit by AWE configration.|||

Hi,

You can check the article at http://www.kodyaz.com/articles/article.aspx?articleid=41 for sp_configure 'awe enabled' usage and editing boot.ini file for /3GB switch.

Eralper

Memory of SQL server

The configuration of SQL server is
1. Windows 2000 server Standard version
2. SQL server 2000 standard
3. 3G memory
4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
i have allocated all the memory to the SQL server at Enterprise manager.
i find at task manager the SQL uses up 1.8G memory at maximum.
The database size at disk is 600m
i find the server runs a query at 30 minutes.
After reboot, the query runs at 15 minutues, maybe release of some memory.
Can i say the memory is not sufficient for the SQL?
If yes, i know there is memory limitation from standard version of Windows
2K and SQL 2K, should i consider Enterprise version of Windows Server and
SQL to allow more memory.
Grateful if you could kindly give me some advices. Thanks.
TonyTony
First of all I'd suggest you staring to tune a 'bad' query , see if the
optomizer uses indexes , how efficient is an execution plan?
"tony wong" <x34@.netvigator.com> wrote in message
news:ebKNJg3NHHA.5064@.TK2MSFTNGP04.phx.gbl...
> The configuration of SQL server is
> 1. Windows 2000 server Standard version
> 2. SQL server 2000 standard
> 3. 3G memory
> 4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
> i have allocated all the memory to the SQL server at Enterprise manager.
> i find at task manager the SQL uses up 1.8G memory at maximum.
> The database size at disk is 600m
> i find the server runs a query at 30 minutes.
> After reboot, the query runs at 15 minutues, maybe release of some memory.
> Can i say the memory is not sufficient for the SQL?
> If yes, i know there is memory limitation from standard version of Windows
> 2K and SQL 2K, should i consider Enterprise version of Windows Server and
> SQL to allow more memory.
> Grateful if you could kindly give me some advices. Thanks.
> Tony
>|||On Sun, 14 Jan 2007 09:05:08 +0800, "tony wong" <x34@.netvigator.com>
wrote:
>The configuration of SQL server is
>1. Windows 2000 server Standard version
>2. SQL server 2000 standard
>3. 3G memory
>4. mainly run by IIS and SQL, sometimes word or excel triggered by IIS.
>i have allocated all the memory to the SQL server at Enterprise manager.
>i find at task manager the SQL uses up 1.8G memory at maximum.
>The database size at disk is 600m
>i find the server runs a query at 30 minutes.
>After reboot, the query runs at 15 minutues, maybe release of some memory.
>Can i say the memory is not sufficient for the SQL?
>If yes, i know there is memory limitation from standard version of Windows
>2K and SQL 2K, should i consider Enterprise version of Windows Server and
>SQL to allow more memory.
>Grateful if you could kindly give me some advices. Thanks.
Generally you'd expect it to run slower after reboot, as the cache
would be empty and physical IO is a lot slower.
I'd say Uri has the right idea, you might be producing a huge join
that preferentially should be kept in memory, or missing an index that
causes some other unnatural memory/cache configuration to be optimal.
Generally these can be addressed by tuning the query.
Of course, a multitude of sins can be hidden if you have enough RAM!
J.