Speed improvement with split

[Petr Danes]

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a", "and",
"the" and so on. There are three main relations - file names, words and a
linking table (each file has many words, each word can be in many files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below is a
sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));


More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok)) INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or (Mid([WT1].[TagWord],4))="America")
AND ((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never split
a database or used SQL server, so as a first try I just ran the splitting
wizard and split the data off to another Access database. The performance
improvement from just this one step is unbelievable - queries that took over
a minute are now done in a few seconds! Does this make sense to anyone? All
the posts on this topic I've seen in the archives complain about reduced
performance after splitting, and recognized experts in these forums
generally agree that reduced performance is part of the price you pay for
the other benefits of splitting; never that performance is an added benefit
of same.

It seems more likely to me that I had some flaw in my original design that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After C&R the
size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement, of
course, but rather at a loss to explain it.

Pete

 

[Jeff Boyce]

Any chance your original table lacked indexing and your SQL-Server version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a", "and",
"the" and so on. There are three main relations - file names, words and a
linking table (each file has many words, each word can be in many files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));


More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok)) INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

Pete

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

 

[Petr Danes]

Natural thought, but no, I had indexes in the original as well. Thanks for
the suggestion, though.

Pete

Any chance your original table lacked indexing and your SQL-Server version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and", "the" and so on. There are three main relations - file names, words
and a linking table (each file has many words, each word can be in many
files). The words relation has over 600,000 entries, the linking relation
over 7 million. Simple searches go well, but more complicated ones were
rather slow, since they involve SQL created on the fly, with self-joins.
Below is a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));


More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts in these forums generally agree that reduced performance is part
of the price you pay for the other benefits of splitting; never that
performance is an added benefit of same.

It seems more likely to me that I had some flaw in my original design
that splitting somehow bypassed, but I can't think what it might be. I
did compact and repair the original database after loading the data,
since the size before C&R was over 1.5 GB, pushing the 2GB Access limit.
After C&R the size is 400MB, but there is no noticeable speed
improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

Pete

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

 

[leoladouceur]

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a", "and",
"the" and so on. There are three main relations - file names, words and a
linking table (each file has many words, each word can be in many files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));


More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok)) INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

Pete

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

 

[aaron.kempf]

I've been seeing this large of an improvement with SQL Server for the
past decade.

I would reccomend learning more about SQL Server; it is a quite
exciting service!

I've done of lot of similiar 'word indexing' as you're talking about..
might you be open to trading notes / schemas?

Thanks

-Aaron

Natural thought, but no, I had indexes in the original as well. Thanks for
the suggestion, though.

Pete

"Jeff Boyce" <[email protected]> pí¹e v diskusním pøíspìvku

Any chance your original table lacked indexing and your SQL-Server version
has automatic index-creation turned on?

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and", "the" and so on. There are three main relations - file names, words
and a linking table (each file has many words, each word can be in many
files). The words relation has over 600,000 entries, the linking relation
over 7 million. Simple searches go well, but more complicated ones were
rather slow, since they involve SQL created on the fly, with self-joins.
Below is a sample of some such statements.
Simple select, quite fast:
SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));
More complicated select, not so fast:
SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));
In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts in these forums generally agree that reduced performance is part
of the price you pay for the other benefits of splitting; never that
performance is an added benefit of same.
It seems more likely to me that I had some flaw in my original design
that splitting somehow bypassed, but I can't think what it might be. I
did compact and repair the original database after loading the data,
since the size before C&R was over 1.5 GB, pushing the 2GB Access limit..
After C&R the size is 400MB, but there is no noticeable speed
improvement.
Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.
Pete
--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -

- Show quoted text -

 

[Larry Linson]

We have already read the original of Petr's post; if you are going to reply
to a post, please add something to it, and don't just send the copied text.
Thanks.

Larry Linson
Microsoft Office Access MVP

I have a database that is coming close to straining Access's capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and", "the" and so on. There are three main relations - file names, words
and a linking table (each file has many words, each word can be in many
files). The words relation has over 600,000 entries, the linking relation
over 7 million. Simple searches go well, but more complicated ones were
rather slow, since they involve SQL created on the fly, with self-joins.
Below is a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));


More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts in these forums generally agree that reduced performance is part
of the price you pay for the other benefits of splitting; never that
performance is an added benefit of same.

It seems more likely to me that I had some flaw in my original design
that splitting somehow bypassed, but I can't think what it might be. I
did compact and repair the original database after loading the data,
since the size before C&R was over 1.5 GB, pushing the 2GB Access limit.
After C&R the size is 400MB, but there is no noticeable speed
improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

Pete

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.

 

[aaron.kempf]

Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's capabilities..
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a", "and",
"the" and so on. There are three main relations - file names, words and a
linking table (each file has many words, each word can be in many files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));

More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok))ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable - queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -

- Show quoted text -

 

[Jeff Boyce]

Aaron

I first wondered if I'd mis-heard (or misrepresented) what my SQL DBAs had
told me. After all, I use SQL-Server but that isn't my primary job.

I checked via Google (SQL-Server automatic indexing) and found a reference
to what appears to be an internal/Microsoft blog, which included:

SQL Server has long shipped a program to help with physical database design
called the Index Tuning Wizard (and now the Database Tuning Advisor in SQL
Server 2005). This can help find a reasonably optimal index set for a set
of queries. It works by running the Optimizer with a series of "what if"
scenarios and evaluating the cost of the plan the optimizer picked in each
case. It then reasons about the best set of indexes to match the workload.



There is also a newer mechanism that you can use in SQL Server 2005 that
compliments the existing Database Tuning Advisor Tool. It does not require
that you pre-identify a workload, and it is integrated into the engine and
runs as part of the regular operation of the server (you do not need to run
anything). It does not do as much work as the DTA, but it can find the
common problems that cause significant performance problems in deployed
systems. The development team used this to debug a number of customer
applications and found that it did identify "better" indexes in a number of
cases. If you have to debug a live system where the physical database
design is not known to be close to optimal, this may be a useful tool to
help improve the system performance.



The specific details of the DMVs are documented here:

http://msdn2.microsoft.com/en-US/library/ms345434(SQL.90).aspx

http://msdn2.microsoft.com/en-US/library/ms345407(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms345421(SQL.90).aspx



This probably needs more investigation, but it seems to imply that there are
both automatic and SQL-DBA-initiated tools for helping with SQL-Server
indexing.



Regards



Jeff Boyce

Microsoft Office/Access MVP





Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's
capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and",
"the" and so on. There are three main relations - file names, words and a
linking table (each file has many words, each word can be in many files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below
is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));

More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off the
data to SQL server and see if a more robust database engine (on the same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable -
queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that
performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design
that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since
the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the improvement,
of course, but rather at a loss to explain it.

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -

- Show quoted text -

 

[Guest]

Hello Jeff,

thank you for the articles. Interesting reading, although not really
pertinent to my situation. If you look at my original post, I ran the
splitter wizard and split the data off to another ACCESS database, not SQL.
I have not worked with SQL before, so as a first attempt, I ran the splitter
off into another Access db, which is why I was so surprised with the
performance improvement.

The only thing which occurs to me is that the two instances of Access use
more RAM, thus allowing faster reads. A single instance of Acess seems
unwilling to use more RAM than about 500MB, despite the machine having
plenty more to spare. Maybe two instances use more, the FE has lots of stuff
in temporary tables and forms, leaving less for data, where the BE is fully
dedicated to data retrieval, allowing it to use all allowable RAM for the
task and so do it faster. I'm reaching, but I have not been able to think of
anything else.

Pete

Aaron

I first wondered if I'd mis-heard (or misrepresented) what my SQL DBAs had
told me. After all, I use SQL-Server but that isn't my primary job.

I checked via Google (SQL-Server automatic indexing) and found a reference
to what appears to be an internal/Microsoft blog, which included:

SQL Server has long shipped a program to help with physical database
design called the Index Tuning Wizard (and now the Database Tuning Advisor
in SQL Server 2005). This can help find a reasonably optimal index set
for a set of queries. It works by running the Optimizer with a series of
"what if" scenarios and evaluating the cost of the plan the optimizer
picked in each case. It then reasons about the best set of indexes to
match the workload.



There is also a newer mechanism that you can use in SQL Server 2005 that
compliments the existing Database Tuning Advisor Tool. It does not
require that you pre-identify a workload, and it is integrated into the
engine and runs as part of the regular operation of the server (you do not
need to run anything). It does not do as much work as the DTA, but it can
find the common problems that cause significant performance problems in
deployed systems. The development team used this to debug a number of
customer applications and found that it did identify "better" indexes in a
number of cases. If you have to debug a live system where the physical
database design is not known to be close to optimal, this may be a useful
tool to help improve the system performance.



The specific details of the DMVs are documented here:

http://msdn2.microsoft.com/en-US/library/ms345434(SQL.90).aspx

http://msdn2.microsoft.com/en-US/library/ms345407(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms345421(SQL.90).aspx



This probably needs more investigation, but it seems to imply that there
are both automatic and SQL-DBA-initiated tools for helping with SQL-Server
indexing.



Regards



Jeff Boyce

Microsoft Office/Access MVP





Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server
version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's
capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and",
"the" and so on. There are three main relations - file names, words and
a
linking table (each file has many words, each word can be in many
files).
The words relation has over 600,000 entries, the linking relation over 7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below
is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));

More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off
the
data to SQL server and see if a more robust database engine (on the
same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable -
queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that
performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design
that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since
the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After
C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the
improvement,
of course, but rather at a loss to explain it.

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -

- Show quoted text -

 

[Jeff Boyce]

Pete

I was responding to Aaron's question. Sorry, you're right, not really
relevant to your original post.

Hello Jeff,

thank you for the articles. Interesting reading, although not really
pertinent to my situation. If you look at my original post, I ran the
splitter wizard and split the data off to another ACCESS database, not
SQL. I have not worked with SQL before, so as a first attempt, I ran the
splitter off into another Access db, which is why I was so surprised with
the performance improvement.

The only thing which occurs to me is that the two instances of Access use
more RAM, thus allowing faster reads. A single instance of Acess seems
unwilling to use more RAM than about 500MB, despite the machine having
plenty more to spare. Maybe two instances use more, the FE has lots of
stuff in temporary tables and forms, leaving less for data, where the BE
is fully dedicated to data retrieval, allowing it to use all allowable RAM
for the task and so do it faster. I'm reaching, but I have not been able
to think of anything else.

Pete

Aaron

I first wondered if I'd mis-heard (or misrepresented) what my SQL DBAs
had told me. After all, I use SQL-Server but that isn't my primary job.

I checked via Google (SQL-Server automatic indexing) and found a
reference to what appears to be an internal/Microsoft blog, which
included:

SQL Server has long shipped a program to help with physical database
design called the Index Tuning Wizard (and now the Database Tuning
Advisor in SQL Server 2005). This can help find a reasonably optimal
index set for a set of queries. It works by running the Optimizer with a
series of "what if" scenarios and evaluating the cost of the plan the
optimizer picked in each case. It then reasons about the best set of
indexes to match the workload.



There is also a newer mechanism that you can use in SQL Server 2005 that
compliments the existing Database Tuning Advisor Tool. It does not
require that you pre-identify a workload, and it is integrated into the
engine and runs as part of the regular operation of the server (you do
not need to run anything). It does not do as much work as the DTA, but
it can find the common problems that cause significant performance
problems in deployed systems. The development team used this to debug a
number of customer applications and found that it did identify "better"
indexes in a number of cases. If you have to debug a live system where
the physical database design is not known to be close to optimal, this
may be a useful tool to help improve the system performance.



The specific details of the DMVs are documented here:

http://msdn2.microsoft.com/en-US/library/ms345434(SQL.90).aspx

http://msdn2.microsoft.com/en-US/library/ms345407(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms345421(SQL.90).aspx



This probably needs more investigation, but it seems to imply that there
are both automatic and SQL-DBA-initiated tools for helping with
SQL-Server indexing.



Regards



Jeff Boyce

Microsoft Office/Access MVP





Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server
version
has automatic index-creation turned on?

Regards

Jeff Boyce
Microsoft Office/Access MVP





I have a database that is coming close to straining Access's
capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and",
"the" and so on. There are three main relations - file names, words and
a
linking table (each file has many words, each word can be in many
files).
The words relation has over 600,000 entries, the linking relation over
7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below
is
a sample of some such statements.

Simple select, quite fast:

SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));

More complicated select, not so fast:

SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok))
ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));

In an attempt to improve performance, I decided to try splitting off
the
data to SQL server and see if a more robust database engine (on the
same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database.
The
performance improvement from just this one step is unbelievable -
queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts
in these forums generally agree that reduced performance is part of
the
price you pay for the other benefits of splitting; never that
performance
is an added benefit of same.

It seems more likely to me that I had some flaw in my original design
that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since
the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After
C&R
the size is 400MB, but there is no noticeable speed improvement.

Does anyone have any thoughts on this? I'm pleased with the
improvement,
of course, but rather at a loss to explain it.

Pete

--
This e-mail address is fake, to keep spammers and their
auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and
I
use
yahoo mail. But please use the newsgroup when possible, so that all
may
benefit from the exchange of ideas.- Hide quoted text -

- Show quoted text -

 

[aaron_kempf]

how do you manage to use 500mb of ram with Access?

that is the funniest thing I've ever heard of in my life.

I'm not even sure i've ever got 'SQL Server Management Studio' up that
high.

-Aaron

Hello Jeff,

thank you for the articles. Interesting reading, although not really
pertinent to my situation. If you look at my original post, I ran the
splitter wizard and split the data off to another ACCESS database, not SQL..
I have not worked with SQL before, so as a first attempt, I ran the splitter
off into another Access db, which is why I was so surprised with the
performance improvement.

The only thing which occurs to me is that the two instances of Access use
more RAM, thus allowing faster reads. A single instance of Acess seems
unwilling to use more RAM than about 500MB, despite the machine having
plenty more to spare. Maybe two instances use more, the FE has lots of stuff
in temporary tables and forms, leaving less for data, where the BE is fully
dedicated to data retrieval, allowing it to use all allowable RAM for the
task and so do it faster. I'm reaching, but I have not been able to think of
anything else.

Pete

"Jeff Boyce" <[email protected]> pí¹e v diskusním pøíspìvku

Aaron

I first wondered if I'd mis-heard (or misrepresented) what my SQL DBAs had
told me.  After all, I use SQL-Server but that isn't my primary job.

I checked via Google (SQL-Server automatic indexing) and found a reference
to what appears to be an internal/Microsoft blog, which included:

SQL Server has long shipped a program to help with physical database
design called the Index Tuning Wizard (and now the Database Tuning Advisor
in SQL Server 2005).  This can help find a reasonably optimal index set
for a set of queries.  It works by running the Optimizer with a seriesof
"what if" scenarios and evaluating the cost of the plan the optimizer
picked in each case.  It then reasons about the best set of indexes to
match the workload.

There is also a newer mechanism that you can use in SQL Server 2005 that
compliments the existing Database Tuning Advisor Tool.  It does not
require that you pre-identify a workload, and it is integrated into the
engine and runs as part of the regular operation of the server (you do not
need to run anything).  It does not do as much work as the DTA, but itcan
find the common problems that cause significant performance problems in
deployed systems.  The development team used this to debug a number of
customer applications and found that it did identify "better" indexes ina
number of cases.  If you have to debug a live system where the physical
database design is not known to be close to optimal, this may be a useful
tool to help improve the system performance.

The specific details of the DMVs are documented here:



This probably needs more investigation, but it seems to imply that there
are both automatic and SQL-DBA-initiated tools for helping with SQL-Server
indexing.

Jeff Boyce

Microsoft Office/Access MVP

Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server
version
has automatic index-creation turned on?
Regards
Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's
capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and",
"the" and so on. There are three main relations - file names, words and
a
linking table (each file has many words, each word can be in many
files).
The words relation has over 600,000 entries, the linking relation over7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below
is
a sample of some such statements.
Simple select, quite fast:
SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));
More complicated select, not so fast:
SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));
In an attempt to improve performance, I decided to try splitting off
the
data to SQL server and see if a more robust database engine (on the
same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable -
queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that
performance
is an added benefit of same.
It seems more likely to me that I had some flaw in my original design
that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since
the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After
C&R
the size is 400MB, but there is no noticeable speed improvement.
Does anyone have any thoughts on this? I'm pleased with the
improvement,
of course, but rather at a loss to explain it.
Pete
--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' andI
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[aaron_kempf]

Access doesn't utilize RAM.

SQL Server will generally double in speed with twice the memory (it
stores frequently read data in memory-- Access doesn't)

I'd just reccomend -- that if you're just starting out-- start with
SQL Server.

Nobody in their right mind would learn Access MDB or ACCDB in the year
2008.

-Aaron

Hello Jeff,

thank you for the articles. Interesting reading, although not really
pertinent to my situation. If you look at my original post, I ran the
splitter wizard and split the data off to another ACCESS database, not SQL..
I have not worked with SQL before, so as a first attempt, I ran the splitter
off into another Access db, which is why I was so surprised with the
performance improvement.

The only thing which occurs to me is that the two instances of Access use
more RAM, thus allowing faster reads. A single instance of Acess seems
unwilling to use more RAM than about 500MB, despite the machine having
plenty more to spare. Maybe two instances use more, the FE has lots of stuff
in temporary tables and forms, leaving less for data, where the BE is fully
dedicated to data retrieval, allowing it to use all allowable RAM for the
task and so do it faster. I'm reaching, but I have not been able to think of
anything else.

Pete

"Jeff Boyce" <[email protected]> pí¹e v diskusním pøíspìvku

Aaron

I first wondered if I'd mis-heard (or misrepresented) what my SQL DBAs had
told me.  After all, I use SQL-Server but that isn't my primary job.

I checked via Google (SQL-Server automatic indexing) and found a reference
to what appears to be an internal/Microsoft blog, which included:

SQL Server has long shipped a program to help with physical database
design called the Index Tuning Wizard (and now the Database Tuning Advisor
in SQL Server 2005).  This can help find a reasonably optimal index set
for a set of queries.  It works by running the Optimizer with a seriesof
"what if" scenarios and evaluating the cost of the plan the optimizer
picked in each case.  It then reasons about the best set of indexes to
match the workload.

There is also a newer mechanism that you can use in SQL Server 2005 that
compliments the existing Database Tuning Advisor Tool.  It does not
require that you pre-identify a workload, and it is integrated into the
engine and runs as part of the regular operation of the server (you do not
need to run anything).  It does not do as much work as the DTA, but itcan
find the common problems that cause significant performance problems in
deployed systems.  The development team used this to debug a number of
customer applications and found that it did identify "better" indexes ina
number of cases.  If you have to debug a live system where the physical
database design is not known to be close to optimal, this may be a useful
tool to help improve the system performance.

The specific details of the DMVs are documented here:



This probably needs more investigation, but it seems to imply that there
are both automatic and SQL-DBA-initiated tools for helping with SQL-Server
indexing.

Jeff Boyce

Microsoft Office/Access MVP

Jeff;

I've never heard of this option in SQL Server.

Are you talking about statistics?

Or the database tuning advisor (aka index tuning wizard)?

Or 'Dynamic Management Views (Select * from
sys.Dm_Db_Missing_index_details).

I love all of the 'automatic indexing' features in SQL Server but I
really honestly don't think that there is a checkbox that enables
automatic index creation.

Is there?

Thanks for the clarification

-Aaron

Any chance your original table lacked indexing and your SQL-Server
version
has automatic index-creation turned on?
Regards
Jeff Boyce
Microsoft Office/Access MVP

I have a database that is coming close to straining Access's
capabilities.
It reads in about 2500 text files (library catalogs) and indexes the
location of each word in each file, excluding trivialities like "a",
"and",
"the" and so on. There are three main relations - file names, words and
a
linking table (each file has many words, each word can be in many
files).
The words relation has over 600,000 entries, the linking relation over7
million. Simple searches go well, but more complicated ones were rather
slow, since they involve SQL created on the fly, with self-joins. Below
is
a sample of some such statements.
Simple select, quite fast:
SELECT
HSBlokSoubor.TagWordAutoID,
HSBlokSoubor.SouborAutoID,
HSBlokSoubor.HSBlok
FROM
Soubory
INNER JOIN
HSBlokSoubor
ON
Soubory.SouborAutoID = HSBlokSoubor.SouborAutoID
WHERE
(((Soubory.OmezeneHledani)=True));
More complicated select, not so fast:
SELECT
HB1.SouborAutoID,
HB1.HSBlok,
WT1.TagWord AS TagWord1,
WT2.TagWord AS TagWord2,
WT3.TagWord AS TagWord3
INTO
NalezeneSoubory
FROM
WordTag AS WT1
INNER JOIN
((HSBlokSoubor AS HB1
INNER JOIN
(WordTag AS WT2
INNER JOIN
HSBlokSoubor AS HB2
ON
WT2.TagWordAutoID = HB2.TagWordAutoID)
ON
(HB1.SouborAutoID = HB2.SouborAutoID) AND (HB1.HSBlok = HB2.HSBlok))
INNER
JOIN
(WordTag AS WT3 INNER JOIN HSBlokSoubor AS HB3
ON
WT3.TagWordAutoID = HB3.TagWordAutoID)
ON
(HB2.SouborAutoID = HB3.SouborAutoID) AND (HB2.HSBlok = HB3.HSBlok)) ON
WT1.TagWordAutoID = HB1.TagWordAutoID
WHERE
(((Mid([WT1].[TagWord],4))="amerika" Or
(Mid([WT1].[TagWord],4))="America") AND
((Mid([WT2].[TagWord],4))="rakouska")
AND ((Mid([WT3].[TagWord],4))="francie"));
In an attempt to improve performance, I decided to try splitting off
the
data to SQL server and see if a more robust database engine (on the
same
computer, no network involved) would better handle the job. I've never
split a database or used SQL server, so as a first try I just ran the
splitting wizard and split the data off to another Access database. The
performance improvement from just this one step is unbelievable -
queries
that took over a minute are now done in a few seconds! Does this make
sense to anyone? All the posts on this topic I've seen in the archives
complain about reduced performance after splitting, and recognized
experts
in these forums generally agree that reduced performance is part of the
price you pay for the other benefits of splitting; never that
performance
is an added benefit of same.
It seems more likely to me that I had some flaw in my original design
that
splitting somehow bypassed, but I can't think what it might be. I did
compact and repair the original database after loading the data, since
the
size before C&R was over 1.5 GB, pushing the 2GB Access limit. After
C&R
the size is 400MB, but there is no noticeable speed improvement.
Does anyone have any thoughts on this? I'm pleased with the
improvement,
of course, but rather at a loss to explain it.
Pete
--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' andI
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -