IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Why does something this simple, take soo long?
It is M$-Access2000 (yuck) and M$-SQL Runtime.

I have a single table query, the table only has 22K records in it, a runtime of MS-SQL through ODBC. Index on PROPNUM, index on 4 other fields as well but none are used.
SELECT PROPNUM, TAXADESC, TXMTBLL, TAXADESC1, TXMTBLL1, TAXADESC2, TXMTBLL2, TAXADESC3, TXMTBLL3, TAXADESC4, TXMTBLL4, TAXADESC5, TXMTBLL5, TAXADESC6, TXMTBLL6, TAXADESC7, TXMTBLL7, TAXADESC8, TXMTBLL8, TAXADESC9, TXMTBLL9, TAXADESC10, TXMTBLL10, TAXADESC11, TAXAMTBILL, TAXADESC12, TXMTBLL11, TAXADESC13, TXMTBLL12, TAXADESC14, TXMTBLL13
FROM SomeUglyTablewith220fields
WHERE ((TXMTBLL8) Is Not "0")
ORDER BY TAXAMTBILL DESC, TXMTBLL10 DESC, TXMTBLL9 DESC, TXMTBLL8 DESC, TXMTBLL7 DESC;

This take in excess of 1 minute. Same amount of time if I just grab everything.

If I export the table and import to PG7.4... and use pgsql. BAM. Less than a second, I get results. Either with the qualifier or not.

I thought, these kinds of things were supposed to be quick. Am I assuming too much from M$ SQL? Or is it the M$ ACCESS?

My mind wants to know. (Other might, too)
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
Here is an example: [link|http://www.greymagic.com/security/advisories/gm001-ie/|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
New Probably at the Access level
My experience is that almost all problems like that are at the Access level. MS SQL isn't the most powerful SQL database in the world, but it is perfectly servicable for simple stuff.

Just a guess, does Access think TXMTBLL8 is a text field? Access might be trying to do string searches on the field rather then integer compares.

Jay
New Doesn't matter either way.
It does happen to be a text field treated as a number.

I made another table that uses fields as numbers. Still same results.

I was watching the runtime... it didn't tell anything spectacular or anything really meaningful. Matter of fact it basically told me nothing.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
Here is an example: [link|http://www.greymagic.com/security/advisories/gm001-ie/|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
New Investigate Pass-Through Queries in Access
--
Chris Altmann
New Thanks. I do that later.
Imagine, Access to do things the right way... err better way I mean. :)
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
Here is an example: [link|http://www.greymagic.com/security/advisories/gm001-ie/|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
New Latency between Access and SQL Server
I'll bet that Access is doing something stupid like fetching each row from SQL server locally in a synchronous way, so you have to wait for 22,000 "please gimme another row", "here's your row", "thank you" transactions. If that silly little round-trip takes .003s, then there's your minute.

Note that when you attach tables through ODBC, Access cannot use any of the indexes on the query. It just fetches the data locally and does full table scans.

As already suggested, look up passthrough queries and use them.

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New That is just WRONG.
Cannot use indexes.

That BLOWS.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
Here is an example: [link|http://www.greymagic.com/security/advisories/gm001-ie/|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
New Sometimes faster just to import the whole table
and ditz with it locally from there. Access tends to go overboard on the locking as well, which can cause its own set of problems.
New I would ask for a showplan
or something similar..(can't remember what the oracle equiv is off-hand).

(Showplan is sybase)

Basically with a showplan, you give the database a query and then it tells you how it's going about getting the data (rather than actually going and getting the data).

This will determine if the problem is in the SQL query itself (ie: on the server) or in the transport mechanism (ie: the ODBC call).

I susppose that MS-SQL doesn't have an ISQL interface?
New There is an osql
and a SET SHOWPLAN_TEXT (or SHOWPLAN_ALL)

MSDE (what I assume is meant by "runtime") should have the osql CLI tool.

There's also a registry setting to turn plan logging on for the Jet engine that Access uses.

[link|http://builder.com.com/5100-6388-5064388.html|http://builder.com.c...6388-5064388.html]

[edit: removed restatment of your statement :)]
--
Chris Altmann
Expand Edited by altmann Dec. 7, 2004, 06:43:59 PM EST
New The answer is in the first line of your post



"The significant problems we face cannot be solved at the same level of thinking we were at when we created them."     --Albert Einstein

"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses."     --George W. Bush
Expand Edited by tuberculosis Aug. 21, 2007, 06:34:31 AM EDT
New Clever, cleaver, thine art.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
Here is an example: [link|http://www.greymagic.com/security/advisories/gm001-ie/|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
New Most questions that start, "why does Access......?"
have one and only one answer: Dump Access.

A decent desk-top SQL-based GUI-oriented database is a good and useful idea. The problem is that Microsoft does not want to fix and improve Access because it would cut into SQL-Server sales. This is what happens when your only competition is yourself.
________________
oop.ismad.com
New That might change.
Access, more and more, is becoming a pretty front end GUI for SQL Server, especially now that MSDE has been released. I wouldn't be surprised to eventually see the Access database component boil away entirely eventually, and use MSDE instead.
"Here at Ortillery Command we have at our disposal hundred megawatt laser beams, mach 20 titanium rods and guided thermonuclear bombs. Some people say we think that we're God. We're not God. We just borrowed his 'SMITE' button for our fire control system."
New Not unless VB is integrated with SQL server
There is a lot of legacy Access code out there that uses functions written in VB from within SQL.

Can you do that in SQL server?

Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New Yeesh!
I'll admit to not knowing that much about the dirty tricks that one can play within Access... VB *inside* the DB structure itself? Yeek.
"Here at Ortillery Command we have at our disposal hundred megawatt laser beams, mach 20 titanium rods and guided thermonuclear bombs. Some people say we think that we're God. We're not God. We just borrowed his 'SMITE' button for our fire control system."
New That trick is actually useful..ugly, but useful
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New The poor man's Control Tables
New That's one thing to use it for
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
New Files systems are not much better. Trees too damned limited
________________
oop.ismad.com
New Perish the thought
SQL Server does integrate with VB via the Data Transformation Services, but that's just used for reporting services.
New "In the Next Version"
The next version of SQL Server ("Yukon") as well as it's MSDE replacing offshoot SQL Server Express will support hosting the .Net CLR in SQL Server.

Not the same thing though, in regards to supporting legacy Access code.
--
Chris Altmann
     Why does something this simple, take soo long? - (folkert) - (21)
         Probably at the Access level - (JayMehaffey) - (1)
             Doesn't matter either way. - (folkert)
         Investigate Pass-Through Queries in Access -NT - (altmann) - (1)
             Thanks. I do that later. - (folkert)
         Latency between Access and SQL Server - (ben_tilly) - (2)
             That is just WRONG. - (folkert)
             Sometimes faster just to import the whole table - (ChrisR)
         I would ask for a showplan - (Simon_Jester) - (1)
             There is an osql - (altmann)
         The answer is in the first line of your post -NT - (tuberculosis) - (1)
             Clever, cleaver, thine art. -NT - (folkert)
         Most questions that start, "why does Access......?" - (tablizer) - (9)
             That might change. - (inthane-chan) - (8)
                 Not unless VB is integrated with SQL server - (ben_tilly) - (7)
                     Yeesh! - (inthane-chan) - (4)
                         That trick is actually useful..ugly, but useful -NT - (ben_tilly) - (3)
                             The poor man's Control Tables -NT - (ChrisR) - (2)
                                 That's one thing to use it for -NT - (ben_tilly)
                                 Files systems are not much better. Trees too damned limited -NT - (tablizer)
                     Perish the thought - (ChrisR)
                     "In the Next Version" - (altmann)

Anyone who would spend $5000 on a laptop is either Todd, or out of his mind.
173 ms