FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model

We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
This does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON
|||Yes. I'm interested too!. Please post details.
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON

FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model

We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONThis does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON|||Yes. I'm interested too!. Please post details.
--
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHt
FHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONsql

Fuzzy Group Updates?

Hi there,

Quick Background: I have an SSIS package that reads data from a flat file then runs it through a Fuzzy Grouping component. The result of this Fuzzy Group is put into a SQL server 2005 table.

Question: Over time, the flat file will be adding new records (some that should be added to existing groups) and so I'll need to update my Fuzzy Group table to include these new records. Is there anyway to simply add these new records to the existing Fuzzy Group without changing all of the _key_out values? If I completely regenerate the Fuzzy Group table that will potentially give me different _key_out values correct?

Does this make sense?

Any help would be greatly appreciated!

>>" If I completely regenerate the Fuzzy Group table that will potentially give me different _key_out values correct?"

Correct.

wenyang

|||

Thanks for the reply!

Anyway to preserve the _key_out while still adding records to the groups? Sounds like a complete rebuild of the Fuzzy group is out of the question. Anyway to do this incrementally?

|||

Hi,

Yes, each time you run Fuzzy Grouping with a different set of input rows (or with a different threshold), it is possible that different groupings will result.

If you have run FG once and would like to keep the existing groups, one alternative would be to use Fuzzy Lookup for the incremental input rows. You would basically perform a fuzzy lookup against the output of FG and return the _key_out of the best matching row. You have thus effectively found a group for the new input row. If no match is found above the FL match threshold, then just assign a new unique _key_out to the input row to create a new group.

A slight problem with this approach is that over time all the incremental rows may not be grouped as well as they could be, as the clustering algorithm that Fuzzy Grouping uses to globally pick groupings is not being employed. At that point you may want to just rerun FG and switch to the new groupings.

We are considering adding a feature in the next version that will allow you keep all the old groupings intact.

Let us know if you have any more questions.

Regards,

-Kris

|||Thanks Kris, that will probably suffice for now. Yeah put that in the next version, the FG component is great but it doesn't have much use after the initial run because of this limitation.

funny sql

The following sql updates 300 records(3000 records in the
marc_POt_lu_rd_post_code table) but the select element only returns one row.
I am attempting to update the 3000 rows which it does but it does it
incorrectly in that the results set from the select portion does not match
what the results set returns after the update. I added the extra postcode
criteria in the select to isolate what the update does but it still updates
the 3000 rows. Weird?
UPDATE marc_POt_lu_rd_post_code
SET County_id = c.County_id,
County_desc = c.County_Desc,
Parent_County_Id = c.Parent_County_Id,
Parent_County_desc = c.County_desc,
Sector_Id = d.Sector_Id,
Sector_Desc = d.Sector_Desc,
Area_Id = e.Area_Id,
Area_Desc = e.Area_Desc
-- Select *
FROM Pot_lu_County_Area_PostCodes a,
QUINN_st..GET_BCP_H_POSTCODES b,
Pot_lu_county c,
Pot_lu_Sectors d,
Pot_lu_Areas e
WHERE a.Postcode = b.Four_Char_Post_Codes
AND b.COUNTY = c.County_Desc
AND b.SECTOR = d.Sector_Desc
AND b.AREA = e.Area_Desc
and a.Postcode = b.Four_Char_Post_Codes
and b.Four_Char_Post_Codes = 'mk40'found the issue
"marcmc" wrote:

> The following sql updates 300 records(3000 records in the
> marc_POt_lu_rd_post_code table) but the select element only returns one ro
w.
> I am attempting to update the 3000 rows which it does but it does it
> incorrectly in that the results set from the select portion does not match
> what the results set returns after the update. I added the extra postcode
> criteria in the select to isolate what the update does but it still update
s
> the 3000 rows. Weird?
> UPDATE marc_POt_lu_rd_post_code
> SET County_id = c.County_id,
> County_desc = c.County_Desc,
> Parent_County_Id = c.Parent_County_Id,
> Parent_County_desc = c.County_desc,
> Sector_Id = d.Sector_Id,
> Sector_Desc = d.Sector_Desc,
> Area_Id = e.Area_Id,
> Area_Desc = e.Area_Desc
> -- Select *
> FROM Pot_lu_County_Area_PostCodes a,
> QUINN_st..GET_BCP_H_POSTCODES b,
> Pot_lu_county c,
> Pot_lu_Sectors d,
> Pot_lu_Areas e
> WHERE a.Postcode = b.Four_Char_Post_Codes
> AND b.COUNTY = c.County_Desc
> AND b.SECTOR = d.Sector_Desc
> AND b.AREA = e.Area_Desc
> and a.Postcode = b.Four_Char_Post_Codes
> and b.Four_Char_Post_Codes = 'mk40'
>