problem with join

hi, I have two tables join with each other,
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:

> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>

problem with huge amount of data

Hi,

I've an application, lets call it simply "A", which creates in a Microsoft Sql Database two huge tables.
Lets call them "table1" and "table2"
It safes really much data into this tables.

After application "A" has finished another application is executed which deletes this two tables.

Then application "A" is started again and it will create this two tables again, but the amount of data becomes bigger.
It can only proceed if the tables were deleted completely before and the database is empty.

This is the procedure which I repeat very often, but everytime the amount of data becomes bigger (table1 and table2 becomes bigger).

A couple if times it works fine, but once it seems data becomes too big and application "A" fails.

Mostlikely because the data wasnt removed correctly / completely.

This is my code of deleting the two tables, maybee there is something I have to change:

</p><p> try
{
SqlConnectionStringBuilder builder =
new SqlConnectionStringBuilder("Server=mycomputer\dbname;Integrated Security=SSPI;" +
"Initial Catalog=testing");

builder["Server"] = "(local)\dbname";
builder["Connect Timeout"] = 10;
builder["Trusted_Connection"] = true;
builder["Initial Catalog"] = ((ComponentConfiguration)this.componentConfig).Persistency.DatabaseName;

SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = builder.ConnectionString;
sqlconnection.Open();
SqlCommand cmd1 = new SqlCommand("DROP TABLE table1"); // TO Do delete all tables
SqlCommand cmd2 = new SqlCommand("DROP TABLE table2"); // TO Do delete all tables

cmd1.Connection = sqlconnection;
cmd2.Connection = sqlconnection;

cmd1.ExecuteNonQuery();
Thread.Sleep(7000);
cmd2.ExecuteNonQuery();
Thread.Sleep(7000);
sqlconnection.Close();
Thread.Sleep(3000);
}

catch { }</p><p> </p><p>

Thanks for help!

mulata

Rather than make 2 trips to delete the 2 tables, you could write a stored proc and delete the tables. I dont know what you mean by "huge" and "big" tables. It will be nice if you can provide some metrics as in the number of rows approximately - few hundred million rows? sample code:

CREATE PROC dbo.DeleteTablesAsBEGINSET NOCOUNT ON--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table1'and xtype ='U')DROP TABLE table1--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table2'and xtype ='U')DROP TABLE table2SET NOCOUNT OFFEND

|||

ok so what I have to do is:

clear a database completely, so that it is empty and recent.
as my programm only creates these two tables, table1 and table2 I can also
delete this two tables.

but its important that it is done fast and reliable.
and it must be done from a .NET program, because the name of the database and server is stored in the .NET application

table1 is small, only 1 row und 0,008 MB.
table2 has about 200000 - 300000 rows and a data space of about 33 GB

thanks,

mulata

|||

Then you would need to make 2 separate connections, each time open the connection, drop the table, close the connetion, in succession.

Would you have only 2 tables or is there possibility of more objects being created?

|||

well at the moment only this two tables.

what is the advantage of opening an own connection for each drop command?

how long takes the execution of a command which deletes a table with about 300000 rows and 33 GB data?

|||

Deleting 300,000 rows should not take long. Generally its not advised to have multiple connections open at the same time. If you have multiple users connecting at the same time your resource pool manager will be busy managing the connection pool. Also, if you have 2 connections, you need to create 2 connection objects. If you use single connection, you can use the same connection object which is less overhead on the framework.

|||

Instead of dropping and recreating the tables as was suggested, I would truncate the tables. This way your structures are left intact and since truncation is a non-logged operation it is lightning fast.

Problem with Grouping

Hi,
I have 2 tables from which I need to get 2 figures:
1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
in the top select and this yields the correct results.
2. Display the Amount using the same formula as above per each Id and here
is where I fail....when I sum theAmount returned from this result set I do
not get the number I get from the first result set which
is -2.4129432084474347 and is correct.
-- This select yields the correct result
select
sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
full join Table2 on Table1.id = Table2.id
order by 1
-- The Amount grouped per each Id seems incorrect
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
order by 1
/*
create table Table1 (Id int, Amount float)
create table Table2 (Id int, Amount float)
insert Table1 select 0, 2466940.7630629078
insert Table1 select 1, 1619341.4993436863
insert Table1 select 2, 294424.12812010606
insert Table1 select 3, 35224.9308604404
insert Table1 select 4, 5816.581840630568
insert Table1 select 5, 9909.2411025063448
insert Table1 select 6, 552720.34837997227
insert Table1 select 7, 20845.780113921814
insert Table1 select 8, 249466.69869616581
insert Table1 select 9, 223489.19351831
insert Table2 select 0, -26748.78485354947
insert Table2 select 1, -444083.44694001391
insert Table2 select 2, -190871.26271638702
insert Table2 select 3, -62978.717071003601
insert Table2 select 4, -4810.138640776684
insert Table2 select 5, -9915.2079038903303
insert Table2 select 6, -305657.70221188507
insert Table2 select 7, -17519.425886078694
insert Table2 select 8, -189198.31576409994
insert Table2 select 9, -70070.519106139123
--DELETE FROM Table1
--DELETE FROM Table2
--drop table Table1
--drop table Table2
*/The behavior you see is correct. The problem is mathematical.
Lets look at a bit of algebra. Your first query creates two sums and
then performs division on the two resuts. This is the equivelent to
the algebraic equation:
(a + b + c) / (x + y +z)
The second query pairs numbers from each set, performs division, then
sums the results:
(a / x) + (b / y) + (c / z)
These are NOT EQUIVELENT to each other. Plug in some numbers.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Hi,
>I have 2 tables from which I need to get 2 figures:
>1. Divide the Total of Table2 by the Total of Table1 and multiple by 10 as
>in the top select and this yields the correct results.
>2. Display the Amount using the same formula as above per each Id and here
>is where I fail....when I sum theAmount returned from this result set I do
>not get the number I get from the first result set which
>is -2.4129432084474347 and is correct.
>
>-- This select yields the correct result
>select
> sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>full join Table2 on Table1.id = Table2.id
>order by 1
>-- The Amount grouped per each Id seems incorrect
>select
> Table1.Id
> ,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
>from Table1
>inner join Table2 on Table1.id = Table2.id
>group by Table1.Id
>order by 1
>
>/*
>create table Table1 (Id int, Amount float)
>create table Table2 (Id int, Amount float)
>insert Table1 select 0, 2466940.7630629078
>insert Table1 select 1, 1619341.4993436863
>insert Table1 select 2, 294424.12812010606
>insert Table1 select 3, 35224.9308604404
>insert Table1 select 4, 5816.581840630568
>insert Table1 select 5, 9909.2411025063448
>insert Table1 select 6, 552720.34837997227
>insert Table1 select 7, 20845.780113921814
>insert Table1 select 8, 249466.69869616581
>insert Table1 select 9, 223489.19351831
>insert Table2 select 0, -26748.78485354947
>insert Table2 select 1, -444083.44694001391
>insert Table2 select 2, -190871.26271638702
>insert Table2 select 3, -62978.717071003601
>insert Table2 select 4, -4810.138640776684
>insert Table2 select 5, -9915.2079038903303
>insert Table2 select 6, -305657.70221188507
>insert Table2 select 7, -17519.425886078694
>insert Table2 select 8, -189198.31576409994
>insert Table2 select 9, -70070.519106139123
>--DELETE FROM Table1
>--DELETE FROM Table2
>--drop table Table1
>--drop table Table2
>*/
>|||Well,
The reason is what Roy said. But if you want that total sum too,
then you can try this query. The column with the ID null will have the total
u wanted.Hope this helps.
select
Table1.Id
,sum(Table2.Amount) / sum(Table1.Amount) * 10 as Total
from Table1
inner join Table2 on Table1.id = Table2.id
group by Table1.Id
with rollup
order by 1|||Thanks, I see.
Any way to achieve what I need?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
> The behavior you see is correct. The problem is mathematical.
> Lets look at a bit of algebra. Your first query creates two sums and
> then performs division on the two resuts. This is the equivelent to
> the algebraic equation:
> (a + b + c) / (x + y +z)
> The second query pairs numbers from each set, performs division, then
> sums the results:
> (a / x) + (b / y) + (c / z)
> These are NOT EQUIVELENT to each other. Plug in some numbers.
> Roy Harvey
> Beacon Falls, CT
> On Thu, 27 Apr 2006 16:50:53 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||I have no idea what you need, as the only information provided is
contradictory.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>Thanks, I see.
>Any way to achieve what I need?
>
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
>|||What is it that you need?
What do these numbers represent and what is it you are trying to calculate?
You could do several things, each one is going to give you different
numbers:
Post a more complete explanation of what you need, along with DDL, sample
data, and desired results, and we will be able to help you.
For an explanation of what I am talking about :
http://www.aspfaq.com/etiquette.asp?id=5006
"yan" <yanive@.rediffmail.com> wrote in message
news:uzq9ihiaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> Thanks, I see.
> Any way to achieve what I need?
>
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:nhj1521k8ufpo28lgfv3fpugiqja0hmbro@.
4ax.com...
as
here
>|||The 2 tables are actually temp tables calculated as part of a report and
contain Totals.
The Id column represnets groups along with each groups amount n the Amount
column.
I need to show the great total (which is -2.4129432084474347) from these
tables which is the formula given in my first post (Table2/Table1*10) and
which yields a correct figure and also the result per each group using the
same formula.
If I run the following select I get the numbers bellow which when I sum I
get a differnrt number than the great total, this is what I had doubts
about :
select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
from Table1 inner join Table2 on Table1.id = Table2.id
order by 1
Id, Amount
--
0 -0.10842897103187299
1 -2.7423705692715177
2 -6.4828675535221034
3 -17.879017937756206
4 -8.2696999244065026
5 -10.006021451413144
6 -5.530060601310776
7 -8.4043033123900095
8 -7.5841110959074793
9 -3.1352978639836735
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
>I have no idea what you need, as the only information provided is
> contradictory.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 21:34:49 +0200, "yan" <yanive@.rediffmail.com>
> wrote:
>|||As Roy stated in his first post, these are mathematically different
calculations.
10 / 2 = 5
20 / 10 = 2
50 / 5 = 10
(10+20+50) / (2+10+5) does not equal (5+2+10)
80 / 17 = 4.7
You cannot do it both ways and get the same answer. Explain the numbers,
what they mean individually, why you are deviding one by the other, and what
the final numbers are supposed to represent.
"yan" <yanive@.rediffmail.com> wrote in message
news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> The 2 tables are actually temp tables calculated as part of a report and
> contain Totals.
> The Id column represnets groups along with each groups amount n the Amount
> column.
> I need to show the great total (which is -2.4129432084474347) from these
> tables which is the formula given in my first post (Table2/Table1*10) and
> which yields a correct figure and also the result per each group using the
> same formula.
> If I run the following select I get the numbers bellow which when I sum I
> get a differnrt number than the great total, this is what I had doubts
> about :
> select Table1.id, Table2.Amount / Table1.Amount *10 AS Amount
> from Table1 inner join Table2 on Table1.id = Table2.id
> order by 1
> Id, Amount
> --
> 0 -0.10842897103187299
> 1 -2.7423705692715177
> 2 -6.4828675535221034
> 3 -17.879017937756206
> 4 -8.2696999244065026
> 5 -10.006021451413144
> 6 -5.530060601310776
> 7 -8.4043033123900095
> 8 -7.5841110959074793
> 9 -3.1352978639836735
>
> --
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:gp3252d2pdhahoriu1mlqpt43krt7bpbeg@.
4ax.com...
10
I
>|||Thank you, you both pointed out my mistake. I knwo what I have to do from
here on.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:er4k94iaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> As Roy stated in his first post, these are mathematically different
> calculations.
> 10 / 2 = 5
> 20 / 10 = 2
> 50 / 5 = 10
> (10+20+50) / (2+10+5) does not equal (5+2+10)
> 80 / 17 = 4.7
> You cannot do it both ways and get the same answer. Explain the numbers,
> what they mean individually, why you are deviding one by the other, and
> what
> the final numbers are supposed to represent.
>
> "yan" <yanive@.rediffmail.com> wrote in message
> news:eJCpuziaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 10
> I
>|||On Thu, 27 Apr 2006 22:07:22 +0200, "yan" <yanive@.rediffmail.com>
wrote:

>I need to show the great total (which is -2.4129432084474347) from these
>tables which is the formula given in my first post (Table2/Table1*10) and
>which yields a correct figure
You have already demonstrated that you can calculate that number.

> and also the result per each group using the
>same formula.
And you can calculate that number for each group.

>If I run the following select I get the numbers bellow which when I sum I
>get a differnrt number than the great total, this is what I had doubts
>about :
"when I sum...". Adding up all those numbers is meaningless. If you
need the number that results from calculating based on all the rows
together, generate that number in a different SELECT.
Roy