Data Querying Speed Question

[Elisha 1]

I started a new job recently where the company is very low tech. They have one official "IT guy". I'm an Analyst working for the CFO and have done work on IT project teams in a Business Intelligence / Data Warehousing environment, but know more about reporting and analysis as opposed to being a developer/programmer/hardware person.

So, my question, so I can explain needs to my (stingy) CFO boss:
Why do queries (e.g. SQL) process faster with an SQL tool (like TOAD, SQL Navigator, Oracle SQL Developer) than the same query with MS-Access? Doesn't Access use T-SQL? MS-Query in Excel seems to process a lot faster with using SQL than Access too. Thanks.

[theonlyski 3]

The only thing I can think of off the top of my head is maybe its indexing the data differently.

Or, could it be because you have less records in the SQL db than you have in the Access

"I may be a dirty pirate hooker...but I'm not about to go stand on the corner." iluvtofly
DPH -7, TDS 578, Muff 5153, SCR 14890
I'm an asshole, and I approve this message

[shropshire 0]

T-SQL isn't the problem per se. It's the querry engine that just can't perform as well in Access as the enterprise strength SQL_Server version.

Y'pays y'money....

(.)Y(.)
Chivalry is not dead; it only sleeps for want of work to do. - Jerome K Jerome

[hypsin 0]

Short answer - if you want stress free life - stay away from Access and drop some dough on enterprise level DB engine.

Long answer - Access has its own castrated version of SQL (Not even close to T-SQL) to query its own Jet DB Engine. It does not scale well (or does not scale at all), has limited querying capabilities and is buggy as hell.

Access is not meant to be used for anything else than quick-and-dirty small scale project (often temporary solution) or purely as a front end with SQL Server/Oracle/Whatever back-end.

Again, if you want stress free life - avoid access at all costs. Speaking from experience.

[Elisha 1]

As to actually doing the querying, I plan on downloading the free Oracle version (Oracle SQL Developer - got the site bookmarked) and I talked to the "IT guy" and says there is plenty of space on our servers.

I don't see Access going away, but I don't see it being the be-all-end-all due to it's weak processing engine. I'm looking for more the "long answer" above - to explain to the boss why Access is slow and not feasible for all situations.

So, does anyone know if the MS-Query that Excel uses is a real strength engine? As I mentioned, the same query in Excel using MS-Query is much faster than Access.

[Remster 24]

While Oracle is a great dB system, its a pain to maintain.

I'm not a M$ junky, by SQL Server 2005 (or even 2008 now) would be my recommendation if you want something that is easy to use and maintain.

In the meantime, if you can handle scripting without all the fancy GUI, try mysql for free.

Remster

[PhreeZone 15]

SQL 2005 Express is free and has almost all the same functions as SQL 2005, its the replacement for MSDE. http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

We use SQL 2005 Express for enterprise level applications since it is a lot easier to develop on then Oracle is and the cost to install a licensed version of Oracle is way more then most people are willing to pay unless they have a large DB that is designed specifically for them. That and its really easy to port from Express to a full SQL 2005 install, just back up the DB from Express and restore as a new session in full version and you are done.

It does have some limitations but unless your DB is going to be over 4 gigs (at which point you better know what the hell you are dong if you want it to run fast with indexed tables) most people can get along fine with Express.

Yesterday is history
And tomorrow is a mystery

Parachutemanuals.com

[marcandalysse 0]

What is the original data source that you are querying? That makes a difference too.

Slow queries are usually not due to the database software, but a result of poor database design and/or poor query statements.

I am an Oracle guy, but in your case,
I second that choice of SQL Express.
Its free. It works well with Access. Less learning curve.

"The reason angels can fly is that they take themselves so lightly." --GK Chesterton

[Elisha 1]

I was told in the interview (and again at work - it's only Day 8 for me and data analysis is the secondary priority - mid to long term projects) that the accounting system (Great Plains) and the transactional system (a Saas based on CO - we get a nightly dump though) run on SQL Server (the nightly copies). I noticed that it is SQL Server 2005 and have confirmed that we have Integration Services (an ETL tool), Analysis (data cube creation and statistical analysis) and Reporting services and there is several gigs of space available on the server. So it looks like the resources are there to begin a nice data warehousing project - I just need to know what I'm doing!
Anyways....still, to answer my questions:

1) Anyone know if the MS-Query tool that uses SQL in Excel is not handicapped compared to what Access uses? (It sure queries faster than Access)

2) I just downloaded the free Oracel SQL Developer tool. I don't see an option to make a data connection to an SQL Server - is it not an option?

[PhreeZone 15]

Oracle does not connect to MS SQL. Hence why we told you to use SQL Express

And Access/Excel use different engines, there is no match between them.

Use the right tool for the job and use the Enterprise Manager in SQL 2005 and the query builder and do the job right from the start.

Yesterday is history
And tomorrow is a mystery

Parachutemanuals.com

[Tokter 0]

Sounds like there is some confusion between the database engine and the frontend that accesses the database going on.

Never used that MS-Query tool in Excel, but the way I understand it, it just sends a SQL query to a database and displays the result in an excel sheet. So I don't see how it can be handicapped, since it's not doing very much, other than import some data into excel.

If you are accessing the same database engine executing the same query, then you should not see much speed difference no matter what tool you're using to display the results.

[Elisha 1]

Guys, please go easy on me...I'm not a db guy. I'm a Reporting/Statistical/Financial Analyst/Business type. I've done GUI report development for BI/DW teams, but don't have much knowledge about dbs, query tuning/performance, etc. beyond basic concepts like indexing.

I think I'm confused between the db and a tool to query data (via SQL). My boss is an accountant and thinks in Access and Excel and knows that Access uses SQL and that using an SQL tool (e.g. TOAD, SQL Navigator, the free Oracle tool I mentioned, etc.) may be necessary, but doesn't really know SQL himself (I'm rather an intermediate myself, but much better than him). Is MySQL both a db and a querying tool? The Oracle SQL Dev tool says it can connect to an SQL Server (I DID say our data sources are on SQL Server 2005).

Btw, there are no tnsnames.ora or sqlnet files on the machine I'm working on.

[Elisha 1]

Quote

Sounds like there is some confusion between the database engine and the frontend that accesses the database going on.

Never used that MS-Query tool in Excel, but the way I understand it, it just sends a SQL query to a database and displays the result in an excel sheet. So I don't see how it can be handicapped, since it's not doing very much, other than import some data into excel.

If you are accessing the same database engine executing the same query, then you should not see much speed difference no matter what tool you're using to display the results.

Saw after I posted...yes, confusion.

[hypsin 0]

Quote

If you are accessing the same database engine executing the same query, then you should not see much speed difference no matter what tool you're using to display the results.

That ^^^. If you are only running pass-through queries (SQL string passed to server, executed by server and results sent back) - then it should not really matter what tool you use - execution time will be the same since it only depends on server horsepower.

Now, if you are "linking" tables in Access and THEN doing some funky SQL with them - then your performance will probably suffer since Access will try to perform some of the execution locally. Also, at this point you will be limited in querying capabilities.

One of my projects involved using Access purely as a front end interface and only using pass-through queries. It worked well, but it was buggy and high-maintenance.

Just 2 kopeks from a fellow dangerous BA.

[Misternatural 0]

Oh shit, it's speed+ data you're talking about-





not speed dating

Beware of the collateralizing and monetization of your desires.
D S #3.1415

[Elisha 1]

OK, I think I understand with Access - a local drive vs server processing issue. But you mention about having Access process pass-through queries - what is that? How do you do that?

[PhreeZone 15]

At this point its probably better to engage a real DBA and SQL expert since unless you know what you are doing its just going to be a series of 20 questions and you won't get anywhere since you don't know enough about the products to make them work correctly.

Yesterday is history
And tomorrow is a mystery

Parachutemanuals.com