Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.
Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

hash?

Hi all,
Are there any hash function build in on SQL 2000?
or the SQL 2003?
Thx.
Rena.Yes, check out CHECKSUM and BINARY_CHECKSUM in BOL.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:Oroy%23C4jEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Are there any hash function build in on SQL 2000?
> or the SQL 2003?
> Thx.
> Rena.
>

hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql

hash table (#) order by problem with more records

We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.

Company Product Priority Prod. QtyProd_Plan_Date
C1 P11100
C1 P22 50
C1 P33 30
C2 P11200
C2 P42 40
C2 P53 10

There is a problem when accessing data for usage priority wise.
Problem is as follows:

We want to plan production date as per group (company) sorted order and
priority wise.

==>With less data, it works fine.
==>But when there are more records for e.g. 100000 or more , it changes
the logical order of data

So plan date calculation gets effected.

==Although I have solved this problem with putting identity column and
checking in where condition.

But, I want to know why this problem is coming.

If anybody have come across this similar problem, please let me know
the reason and your solution.

IS IT SQL SERVER PROBLEM?

Thanks & Regards,
T.S.Negi> when there are more records for e.g. 100000 or more , it changes
> the logical order of data

Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your problem
with DDL (including keys), sample data INSERT statements and show your
required end result.

--
David Portas
SQL Server MVP
--|||While inserting records in hash table. It is already order by on some
fields.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

T.S.Negi

David Portas wrote:
> > when there are more records for e.g. 100000 or more , it changes
> > the logical order of data
> Are you referring to the perceived order in the table? Rows in tables
> have NO logical order in a relational database. If you require a
> particular order you have to query them using a SELECT statement with
> an ORDER BY clause otherwise the ordering is undefined.
> If that doesn't answer your question then please describe your
problem
> with DDL (including keys), sample data INSERT statements and show
your
> required end result.
> --
> David Portas
> SQL Server MVP
> --|||There is an update condition. Which I want to make sure, performing on
ordered data (order by used at the time of insert).
I want to avoide loop.

Reason: "Rows in tables have NO logical order in a relational database"
!!!!

So Please advice.
Thanks,
T.S.Negi

Sample SQL:
===========

UPDATE #WK_PDR_ProcessingData SET
@.Opn_Stock_Qty= CASE WHEN (
@.Customer_Cd = Customer_Cd
AND @.Product_No = Product_No
AND @.Product_Site_Cd = Product_Site_Cd
AND @.Assy_Company_Cd = Assy_Company_Cd
AND @.Assy_Section_Cd = Assy_Section_Cd
AND @.Line_Cd = Line_Cd
) THEN @.Opn_Stock_Qty + @.Production_Qty - @.Requirement_Qty
ELSE begin_Stock_Qty END,
Calc_Stock_Qty= @.Opn_Stock_Qty + Production_Qty - Requirement_Qty,
@.Customer_Cd = Customer_Cd,
@.Product_No = Product_No,
@.Product_Site_Cd= Product_Site_Cd,
@.Assy_Company_Cd= Assy_Company_Cd,
@.Assy_Section_Cd= Assy_Section_Cd,
@.Line_Cd = Line_Cd,
@.Production_Qty = Production_Qty,
@.Requirement_Qty= Requirement_Qty
FROM #WK_PDR_ProcessingData|||tilak.negi@.mind-infotech.com (tilak.negi@.mind-infotech.com) writes:
> While inserting records in hash table. It is already order by on some
> fields.

And once it is inserted, there is no longer any order.

> But when selecting/updating records, I want the same order of records
> should be updated/selected.
> "Rows in tables have NO logical order in a relational database"
> I think, True for hash(#) and permanent table.

Well, obviously you have some operation that does not give you the
desired result, and you posted an UPDATE statement, which is a little
funny, because all you do is to assign a variable.

I suggest that you follow the standard recommendation and post:

o CREATE TABLE statement for your table(s)
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of what ou are trying to achieve.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||UPDATEs are not ordered either. The result of your UPDATE statement is
undefined, unreliable and, in my view, not useful.

Please specify the whole problem rather than post fragments of your
non-working solution. The best way to specify the problem is to post
DDL, sample data and required end results. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view shoul
d
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort o
n
a large table?
--
Regards,
Jamie"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view should
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort on
a large table?
Regards,
Jamie
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David
|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie
sql

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view should
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort on
a large table?
--
Regards,
Jamie"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie

hash join / merge join option

can anyone tell me in what situation you should force a hash join or merge
join option in a query?generally speaking, it's usually a best practice to allow sql server's
optimizer to use whichever type of join it deems to be most efficient
instead of forcing a join.|||I know, but sometimes when I tried to force a merge join, it's actually
faster, although I don't know why.
so can you explain why?
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109792227.328553.193860@.o13g2000cwo.googlegroups.com...
> generally speaking, it's usually a best practice to allow sql server's
> optimizer to use whichever type of join it deems to be most efficient
> instead of forcing a join.
>|||hash joins usually occur when the optimizer cannot find the appropriate
indexes to use to speed up the query.
therefore, when you force an merge join on the query using a particular
column, it will speed up your query. instead of forcing a join hint,
study your query and determine if you can apply any appropriate
indexes. that way, the optimizer will always use the best possible type
of join.
in general, you want your joins to be loop joins, followed by merge
joins and lastly, hash joins.|||I thought loop join is not good but inner join is faster than loop join in
most of the cases.
I think loop join is better than inner join when you call a query which join
tables from a linkedserver
or when a small table joins a big table.
I agree with you that it's bad pratice to force a merge join or hash join.
but as far as merge join or hash join, in case they can't find right
indexes(it happens pretty often actually), I guess they catergorize the
data into small chunks, so it's easier to match the rows between tables.
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
> hash joins usually occur when the optimizer cannot find the appropriate
> indexes to use to speed up the query.
> therefore, when you force an merge join on the query using a particular
> column, it will speed up your query. instead of forcing a join hint,
> study your query and determine if you can apply any appropriate
> indexes. that way, the optimizer will always use the best possible type
> of join.
> in general, you want your joins to be loop joins, followed by merge
> joins and lastly, hash joins.
>|||Loop join and inner join are not mutually exclusive. "Inner" controls the
semantics of how you want rows in the two tables to be related; this is
determined by how you actually write your query. If you don't specify the
semantic type of join, the default is INNER JOIN.
"loop|hash|merge" are the algorithms SQL Server can use internally to
process the join. There is no default.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
>I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
> join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>|||Britney
I replied to this in the .server group. Please do not post the same question
independently in multiple groups, so that people do not waste time answering
questions that have already been answered.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uHk$dz1HFHA.3624@.tk2msftngp13.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>|||i think you're confusing the type of join (inner, outer, left, right
etc) syntax and the algorithm that sql server uses under the hood to
process a particular join.
sql server optimizer is very smart (99% of the time ;))
if it can't find the right indexes, more often than not. you're not
designing your indexes correctly. if you want some tips on what indexes
should generally be created per your query's design, please let me
know.|||britney,
generally speaking, merge joins are used if both inputs are sorted on the
join predicate (ie, if there are tables 'orders' and 'order_details', joined
on 'orderid' column, and both tables are indexed on that column). if there
are no such indexes, sql server will choose loop join, or decide to sort
(one or both) inputs on fly and use merge join, whichever method it thinks
would perform better for a given situation. iow, it is not a good idea to
force use of either merge or loop join - unless you 'force' merge join by
adding the appropriate indexes.
hth
dean
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>

Hash join

Can any one explain me this "Option [hash join]"...
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regards
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regardssql

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

hash in RS

Hi,

I need to take the Social Security number and hash it into a unique identifier because I can't display Social Security numbers on a report.

Does Reporting Services have any encrypting/hashing routines that I can use?

Any suggestions?

Thanks.

You could write some code to do this, however there is no built-in functionality.

If it needs to be secure I would has the data within SQL Server. If you are using SQL 2005 there are some functions to do this.

Here is something that may relate.

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

cheers!

|||

Maybe you can use the vb code in the following link in your report code and call the function to get the encrypted value:

http://www.freevbcode.com/ShowCode.asp?ID=4398

Or you can write a custom code in any of the .net supported language and use Encryption class in the BCL and refer to this custom code (.dll) and call the function again to get the excrypted value back.

Or in a simple way, display just "###-##-####" for all records instead of displaying the encrypted/hashed value because there is always a possibility that the hacker could decrypt the value.

Shyam

Hash files in SQL Server

Hello everyone,
I am trying to test a software called NGS SQL Crack which basically
cracks the passwords for all the userids on your server. For the test
it is asking me to load a "hash file". Have any ideas what they are
referring to?
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!It's not a SQL Server thing. I would read the docs for the software product
you're using. I suspect it explains what the hash file is...
also... I found some info simply by googling the relevent terms...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message news:eGjCpkw%23D
HA.3536@.TK2MSFTNGP10.phx.gbl...
Hello everyone,
I am trying to test a software called NGS SQL Crack which basically
cracks the passwords for all the userids on your server. For the test
it is asking me to load a "hash file". Have any ideas what they are
referring to?
Thanks,
Raziq.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!