VERY URGENT! Large query taking 20mins

[nzrdb6]

Hi Guys, I'm stuck and need an answer for the big boss sharpish.

I have a PT query that points at Sybase. The query takes 20mins to run
and basically is a select blah from tblA, tblB where
tblA.keyfield=tblB.keyfield and <usual where filtering>. tblA and tblB
are massive - about 10m each and are growing every day. The resultset
is only about 22K records big

Our users will go mad with a 20min delay so I had an idea -

#1 Simulate a materialized view by pre-joining the tables in a local
Access table and only inserting changes locally based on a datetime
field in the Sybase tables ie insert into local table where datetime >
the_last_time_i_pulled_the_data.

Can this be done silently ie when the user has the Access db closed a
batch file opens the db in the background invisibly and performs the
inserts? Thus when the db is opened, its good to go.

 

[Stefan Hoffmann]

hi,

I have a PT query that points at Sybase. The query takes 20mins to run
and basically is a select blah from tblA, tblB where
tblA.keyfield=tblB.keyfield and <usual where filtering>. tblA and tblB
are massive - about 10m each and are growing every day. The resultset
is only about 22K records big

No indices on your fields in <usual where filtering>? 10m records should
not be such a problem for Sybase.

btw, why don't you use a join, e.g.

SELECT blah
FROM tblA
INNER JOIN tblB
ON tblA.keyfield=tblB.keyfield
WHERE <usual where filtering>

?

mfG
--> stefan <--

 

[a a r o n . k e m p f]

Doing _ANYTHING_ in Access takes 20 minutes-- it is a piece of crap
database engine.

It's time to lose the training wheels and move to SQL Server.

 

[a a r o n . k e m p f]

and for the record, if you want _REAL_ PivotTables.. then give them
Analysis Services.

any pivotTables _ANYWHERE_ without real OLAP drilldown-- it just can't
compete.

MicroSoft Analysis Services is the fastest product on the market.
Fastest growing-- most popular-- most powerful.

-Aaron

 

[Graham R Seach]

Geez Kempf! Wake up! The OP said his backend is Sybase. His organisation is
NOT going to switch to bloody SQL Server just because YOU say so.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message

 

[Stefan Hoffmann]

Doing _ANYTHING_ in Access takes 20 minutes-- it is a piece of crap
database engine.

Can you read?
The query is executed by Sybase...


mfG
--> stefan <--

 

[Graham R Seach]

I agree with Stefan; I'd be losing the cartesian join in favour of something
with a bit better performance. If as Stefan suggests, you use an inner join,
you'll then be able to make use of the indexes, rather than forcing table
scans. So optimise your query and performance will improve, dramatically.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[So Sorry For Poor Aaron]

I don't, of course, expect a response from the bot that proudced _this_ post,
but I wonder why anyone would use aaron kempf's name in an automated posting.
Wouldn't they want to use the identity of someone who has some credibility?

Aaron hasn't had any credibility here since long before he pled guilty to
cyberstalking. Do you think it might be that the bot's authors noted the
similarity between aaron's only suggestion and the bot's suggestion "use SQL
Server"?

So Sorry For Poor Aaron

 

[a a r o n . k e m p f]

JET against a real db.. is a stupid stupid stupid way of like.
JET makes the query about half as fast.. from all the extra overhead

 

[a a r o n . k e m p f]

yah not with an attitude like that.

He should though. Sybase is silly. The query optimizer sucks from
what I've seen.

-Aaron

 

[a a r o n . k e m p f]

and he's not gonig to move to JET just because some stupid MVP that
cant spell SQL-- says so.

Maybe you should be taking your priveleged status and asking MS to
copy ADP format for Sybase, Oracle and DB2.
then things would run better.

do something constructive-- with your so called 'Most Valuable
Professional' status.
As it is-- Sybase against JET is just plain stupid.

Anything against JET is just plain stupid.

And it's not like he's going to move to SHAREPOINT instead of SQL
Server.

-Aaron

 

[Rick Brandt]

I agree with Stefan; I'd be losing the cartesian join in favour of
something with a bit better performance. If as Stefan suggests, you
use an inner join, you'll then be able to make use of the indexes,
rather than forcing table scans. So optimise your query and
performance will improve, dramatically.

He is not producing a cartesian join. He is just using the alternative SQL
syntax of doing the join within his WHERE clause instead of with the "JOIN"
syntax. Not my preference, but it should not make any difference
performance-wise.

 

[Graham R Seach]

He should though.
That's not your decision to make. That decision is reserved for the CIO of
his company. There are business considerations which you consistently
*choose* to ignore. If you want organisations, or anone else, to "see the
light" and move to SQL Server, then write an educated, supportable and
technically/financially justifiable business case, and send it to them. So
far you have demonstrated little else except a desire to insult peoples'
intelligence and to denigrate a product that, whether you like it or not,
serves a purpose. That may not suit you or your purpose, but no-one is going
to listen to you unless you can demonstrate an ethical and professional
attitude toward *their* business needs.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message
yah not with an attitude like that.

He should though. Sybase is silly. The query optimizer sucks from
what I've seen.

-Aaron

 

[Graham R Seach]

and he's not gonig to move to JET just because some stupid MVP that cantI assume you're referring to me. Firstly, check my spelling. SQL is spelled
correctly. Secondly, I have never advised anyone on these groups to use
Access. I help them arrive at technical/business solutions, which is
something that perhaps you should do in a calm, rational and ethical manner.
What makes you think we haven't? Time and time again you claim you work
for/have worked for Microsoft; why don't you take your privileged status and
do something constructive with it?
I am offended by your denigration of *my* MVP status. You know nothing about
me, my qualifications or my experience. The only thing you know is that I've
received a community award that you would never be able to receive, and that
I don't happen to agreed with you on all topics. Here's some good advice
pal; don't say anything that you wouldn't say to a person's face.
So? Like I said, if you don't like the way the OP's company manages their
data, write to them about it and convince them of the wisdom of following
your expert advice. Don't just whinge about it here.
You've said that a thousand times. We heard you.
No-one said anything about SharePoint. Are you unable to make a single point
and then argue *that* point through to its conclusion? Your scatter gun
policy is reminiscent of the way a child argues. You are an adult - argue
your point in an adult fashion - calmly, respectfully, and with proper
consideration for the OP's problem and situation.

With waning regard,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message
and he's not gonig to move to JET just because some stupid MVP that
cant spell SQL-- says so.

Maybe you should be taking your priveleged status and asking MS to
copy ADP format for Sybase, Oracle and DB2.
then things would run better.

do something constructive-- with your so called 'Most Valuable
Professional' status.
As it is-- Sybase against JET is just plain stupid.

Anything against JET is just plain stupid.

And it's not like he's going to move to SHAREPOINT instead of SQL
Server.

-Aaron

 

[Graham R Seach]

Uhm, it's not going anywhere near Jet; it's a passthrough query.

With waning regard,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message
JET against a real db.. is a stupid stupid stupid way of like.
JET makes the query about half as fast.. from all the extra overhead

 

[a a r o n . k e m p f]

If he's asking for FASTER PIVOT TABLES then he should go to Analysis
Services.

Case closed.

If it is an organization that is stupid enough to sleep with Sybase--
they need a new CIO.
They need to hear the good word of MS SQL Server. Much like a
missionary; I'm here to save these people from eternal damnation.

Sybase sucks.
Oracle sucks
DB2 sucks.

mainly because they don't include the 'whole package'.

Do any of those vendors include:
a) ETL
b) Olap
c) Reporting
d) Performance Monitoring (sysmon / perfmon)
e) Office Web Components, drilldown capabilities for free (with
office)

Does Sybase include these?
Does Access? Access doesn't support drilldown!!!

These people just really need to see the light. Anybody using
anything other than SQL Server-- whether it is JET or EXCEL or
Sybase-- needs to move to SQL Server.

The typical company has 50% of their staff.. typing shit in Excel
every day.

What would happen if 50% of their people learned SQL Server instead of
Excel? THEN THINGS WOULD BE AUTOMATED AND WE WOULD NOT SEND OUR MONEY
TO INDIA TO BUILD DB SHIT.

-Aaron

 

[a a r o n . k e m p f]

By default-- if you're not telling them to use SQL Server-- when
appropriate-- then you're just like all the other dipshit MVPs around
here.

If when someone is bitching about linked tables-- and you don't
reccomend ADP-- then _YOU_ are my ****ing enemy.

Half of the MVPs around here.. just give blatantly WRONG information
about SQL Server. 'oh, don't use identity or unique identifier or
INTEGER datatypes'.

YES YOU ARE IN THE SAME CATEGORY AS THOSE OTHER DIPSHIT MVPs.

About SharePoint?? You ****ing jet crybabies are getting JET shoved
down your throat without knowing what is happening.
A future without ADP means a future WITH SharePoint-- or some other
piece of shit service driven SOA crapware.

Right now, all the MVPs around here-- run around spreading lies about
ADP.
Is it depecrated? _NO_ it is not.

99% of the MVPs run around spreading lies about how it is so complex.

ADP ****ing rock and I'll promote ADP on this newsgroup until the day
I die.

Technically-- you don't have the passion for Access that I do.. so
_WHY_ is it that you get recognized for promoting JET and I don't get
a MVP?
Because MVP program is run my a bunch of senior citizens that want to
bring back DAO.

And seriously here-- why in the **** would any sane company..
UNDEPECRATE JET? UNDEPECRATE DAO?

**** that, they're not allowed to.

-Aaron
MCITP: DBA
I've been using SQL Server every day for a decade.

 

[a a r o n . k e m p f]

I'm sorry. when he says 'PT query' I assume that it is a query he is
using in a PIVOT TABLE.
I guess it's possible that he's using a PASSTHROUGH.

I guess I mis-read it.

But when he's talknig about this:
-----------------------------------------------------------
Simulate a ... view by pre-joining the tables in a local
Access table
-----------------------------------------------------------

I think that it's obvious that he's _NOT_ joining on the sybase side.
It looks to me like he's joining through linked tables.

Anything going to linked tables and then joining is going to run like
crap because jet is stupid.

Sorry