Creating a unique customer code with a function

[mattc66 via AccessMonster.com]

Hi Tom,

I have run it now with the live data around 5000 records. 8meg of data. When
I run the SQL ranking stuff it pegs the processor. After about 5 hours of
just letting it run, i killed the process. It showed that it was still runing.
I compressed the database and tried it again. I get the same result.

I then took the query and had it only select the names starting with the
letter "A*" that worked. So I then opened it up and did a rang. It looks like
it works okay with 200 records, but if it gets around 500 records it takes
about 20-30min to run the ranking.

I have tried it on 2 different machines with the same results. Machine 1: P4
3.0mhz 512k Ram with 80gigs free hard drive. Machine 2: Centrino Laptop 1.?
Mhz 1gig Ram with 60gigs free.

Do you have any thoughts on how I could improve this process?

Thanks
Matt

Dear Matt:

For double the fee (currently $0.00) I will answer in English next time.
Greek is rough on me, too!

Tom Ellison

Hi Tom,

[quoted text clipped - 35 lines]

 

[Tom Ellison]

Dear Matt:

Make sure there is an index on CompanyName.

Your requirements are such that it probably will be slow run this way. I
recommend this:

1. Add a new column to receive the new code value.

2. Index this column.

3. Put the 3 letter value in this column.

4. Replace alphaCode(T1.CompanyName) in the query with this column.

This should make a difference in the speed.

Tom Ellison

Hi Tom,

I have run it now with the live data around 5000 records. 8meg of data.
When
I run the SQL ranking stuff it pegs the processor. After about 5 hours of
just letting it run, i killed the process. It showed that it was still
runing.
I compressed the database and tried it again. I get the same result.

I then took the query and had it only select the names starting with the
letter "A*" that worked. So I then opened it up and did a rang. It looks
like
it works okay with 200 records, but if it gets around 500 records it takes
about 20-30min to run the ranking.

I have tried it on 2 different machines with the same results. Machine 1:
P4
3.0mhz 512k Ram with 80gigs free hard drive. Machine 2: Centrino Laptop
1.?
Mhz 1gig Ram with 60gigs free.

Do you have any thoughts on how I could improve this process?

Thanks
Matt

Dear Matt:

For double the fee (currently $0.00) I will answer in English next time.
Greek is rough on me, too!

Tom Ellison

Hi Tom,

[quoted text clipped - 35 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

The new column name is 3LTR.

Following your suggestion below, it would change the query FROM/TO.

Item# 4 Replace alphaCode(T1.CompanyName) in the query with this column.
FROM>> WHERE alphaCode(T1.CompanyName) = alphaCode(T.CompanyName)
TO>> WHERE (3LTR) = alphaCode(T.CompanyName)

When I do this I get all kinds of nice warnings messages. So I changes it to
([3LTR]). The warnings go away, but the column is blank.

Any suggestions? Or did I not understand your instructions.

Matt

Dear Matt:

Make sure there is an index on CompanyName.

Your requirements are such that it probably will be slow run this way. I
recommend this:

1. Add a new column to receive the new code value.

2. Index this column.

3. Put the 3 letter value in this column.

4. Replace alphaCode(T1.CompanyName) in the query with this column.

This should make a difference in the speed.

Tom Ellison

Hi Tom,

[quoted text clipped - 34 lines]

 

[Tom Ellison]

Dear Matt:

Seeing just a fragment of the query doesn't work so well for me. Please
post the whole thing.

What I'm expecting would be:

SELECT CompanyName, [3LTR],
(SELECT COUNT(*)
FROM coNameTest T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Tom Ellison

Hi Tom,

The new column name is 3LTR.

Following your suggestion below, it would change the query FROM/TO.

Item# 4 Replace alphaCode(T1.CompanyName) in the query with this column.
FROM>> WHERE alphaCode(T1.CompanyName) = alphaCode(T.CompanyName)
TO>> WHERE (3LTR) = alphaCode(T.CompanyName)

When I do this I get all kinds of nice warnings messages. So I changes it
to
([3LTR]). The warnings go away, but the column is blank.

Any suggestions? Or did I not understand your instructions.

Matt

Dear Matt:

Make sure there is an index on CompanyName.

Your requirements are such that it probably will be slow run this way. I
recommend this:

1. Add a new column to receive the new code value.

2. Index this column.

3. Put the 3 letter value in this column.

4. Replace alphaCode(T1.CompanyName) in the query with this column.

This should make a difference in the speed.

Tom Ellison

Hi Tom,

[quoted text clipped - 34 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

I have 3 examples for you below. The first one is based on your suggestion
today on ways to improve the speed.

Example 1: results below Code.

SELECT CompanyName, [3LTR],
(SELECT COUNT(*)
FROM coNameTest T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameTest T

Results>>>>>>>>>>>>>> Company Name is Displayed and the 3LTR is BLANK and No
Ranking.
Fast Results...

CompanyName 3LTR Rank
A C ELECTRIC CORP - BLADENSBURG 0
A COMFORT SERVICE - PITTSBURGH 0
A H STOLER BUILDERS INC - CHALFONT 0
A PLEASURE BREAK - WISCONSIN RAPID 0
A PRO POOL & SPA - LINDSTROM 0
A SIGN CITY - GRAND HAVEN 0

EXAMPLE 2: Add the alphaCode Function and change the WHERE statement, get the
following result.

SELECT alphaCode([CompanyName]) AS [3LTR],
(SELECT COUNT(*)
FROM tblCustomers T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName< T.CompanyName)
AS Rank
FROM tblCustomers AS T;

Results>>>>>>>>>>>>>>>>>>>>>>3LTR Code is populated, but Rank is 0.
Results are FAST...
3LTR Rank
AAA 0
AAA 0
AAA 0
AAN 0
AAP 0
AAP 0
AAP 0
AAR 0
AAS 0
AAZ 0

EXAMPLE 3: This works, but is very slow.. 3LTR is populated and the Ranking
works.
However the results take 4 ever...Hours! I finally did the CNTRL+BREAK and
selected a range to produce this example.

SELECT alphaCode(CompanyName) AS 3LTR,
(SELECT COUNT(*)
FROM tblCustomers T1
WHERE alphaCode(T1.CompanyName) = AlphaCode(T.CompanyName)
AND T1.CompanyName< T.CompanyName)
AS Rank
FROM tblCustomers AS T

Results>>>>>>>>>>>>>>>>>>>>>>>>>>>>As wanted
3LTR Rank
AAA 0
AAA 2
AAA 1
AAN 0
AAP 1
AAP 2
AAP 0
AAR 0
AAS 0
AAZ 0

I can work with this, just wish it didn't take all day to populate the data.

Thanks
Matt

Dear Matt:

Seeing just a fragment of the query doesn't work so well for me. Please
post the whole thing.

What I'm expecting would be:

SELECT CompanyName, [3LTR],
(SELECT COUNT(*)
FROM coNameTest T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Tom Ellison

Hi Tom,

[quoted text clipped - 38 lines]

 

[Tom Ellison]

Dear Matt:

It appears you have created the [3LTR] column, but you have not put the code
into this column. Is this the case? Use an update query to put the code in
[3LTR] now, before running the newer queries I have provided.

UPDATE coNameTest SET CompanyName.[3LTR] = alphaCode([CompanyName]);

Check to see the rows have the code in the [3LTR] column now. This was step
3 in my previous instructions.

Please try the queries again. Any difference?

Tom Ellison

Hi Tom,

I have 3 examples for you below. The first one is based on your suggestion
today on ways to improve the speed.

Example 1: results below Code.

SELECT CompanyName, [3LTR],
(SELECT COUNT(*)
FROM coNameTest T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameTest T

Results>>>>>>>>>>>>>> Company Name is Displayed and the 3LTR is BLANK and
No
Ranking.
Fast Results...

CompanyName 3LTR Rank
A C ELECTRIC CORP - BLADENSBURG 0
A COMFORT SERVICE - PITTSBURGH 0
A H STOLER BUILDERS INC - CHALFONT 0
A PLEASURE BREAK - WISCONSIN RAPID 0
A PRO POOL & SPA - LINDSTROM 0
A SIGN CITY - GRAND HAVEN 0

EXAMPLE 2: Add the alphaCode Function and change the WHERE statement, get
the
following result.

SELECT alphaCode([CompanyName]) AS [3LTR],
(SELECT COUNT(*)
FROM tblCustomers T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName< T.CompanyName)
AS Rank
FROM tblCustomers AS T;

Results>>>>>>>>>>>>>>>>>>>>>>3LTR Code is populated, but Rank is 0.
Results are FAST...
3LTR Rank
AAA 0
AAA 0
AAA 0
AAN 0
AAP 0
AAP 0
AAP 0
AAR 0
AAS 0
AAZ 0

EXAMPLE 3: This works, but is very slow.. 3LTR is populated and the
Ranking
works.
However the results take 4 ever...Hours! I finally did the CNTRL+BREAK and
selected a range to produce this example.

SELECT alphaCode(CompanyName) AS 3LTR,
(SELECT COUNT(*)
FROM tblCustomers T1
WHERE alphaCode(T1.CompanyName) = AlphaCode(T.CompanyName)
AND T1.CompanyName< T.CompanyName)
AS Rank
FROM tblCustomers AS T

Results>>>>>>>>>>>>>>>>>>>>>>>>>>>>As wanted
3LTR Rank
AAA 0
AAA 2
AAA 1
AAN 0
AAP 1
AAP 2
AAP 0
AAR 0
AAS 0
AAZ 0

I can work with this, just wish it didn't take all day to populate the
data.

Thanks
Matt

Dear Matt:

Seeing just a fragment of the query doesn't work so well for me. Please
post the whole thing.

What I'm expecting would be:

SELECT CompanyName, [3LTR],
(SELECT COUNT(*)
FROM coNameTest T1
WHERE T1.[3LTR] = T.[3LTR]
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Tom Ellison

Hi Tom,

[quoted text clipped - 38 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com