jtds blob size

When I try to insert more then 200Mb data into blob field (varbinary) using jtds jdbc-driver I've got an error. Connection is going closed. In fact I cant insert so much data with other tools too (TOAD for MSSQL).Could you try Microsoft JDBC driver for SQLServer 2005? This may work better. In fact we are planning a v 1.1 release this August. This release can handle larger datatypes better.

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>

Join Small Table to Big Table or Vice Versa, does it matter?

If I join Table1 to Table2 with a WHERE condition, is
it the same if I would join Table2 to Table1 considering
that the size of the tables are different.

Let's assume Table2 is much bigger than Table1.

I've never used MERGE, HASH JOINs etc, do any of
these help in this scenario?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

For an inner join the order does not matter.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

These are optimizer hints, and you should use them if you can get
good performance in any other way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.

Yes.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

No.

--
David Portas
SQL Server MVP
--

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:7_%te.82758$Jk6.1151808@.wagner.videotron.net. ..
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?
>
> Thank you|||On Tue, 21 Jun 2005 17:52:02 -0400, serge wrote:

> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
> Let's assume Table2 is much bigger than Table1.

It doesn't matter because the query optimizer will go through thousands of
optimization iterations and choose the best plan it found so far in the
time frame it knows it should not spend any further.

> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?

no.. The query optimizer is smart enough to choose the best plan.

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server