• my name (unregistered)

    So what made the db slow?

  • Aitkiar (unregistered)

    I hate things that encapsulate BBDD operations. They are complex and easy to use improperly and in the end you need to learn how to optimeze not only for the BBDD but for the encapsulating layer as well.

  • Tim (unregistered)

    Regardless of performance problems cause by bad queries, and the obvious WTF of upgrading without testing it first, it would be interesting to know what kind of database upgrade would make the same queries run much slower.

    In my experience database vendors are normally very hot on making sure performance gets better with every release because they have a large number of large corporate customers who would create some serious shit if an upgrade caused the system to run slower

  • Gearhead (unregistered)

    Regardless of performance problems cause by bad queries, and the obvious WTF of upgrading without testing it first, it would be interesting to know what kind of database upgrade would make the same queries run much slower.

    If the upgrade was accompanied by a hardware refresh, it might be a misconfigured switch or other network-related issue.

  • OldCoder (unregistered) in reply to my name

    Because every time .Filter is specifed in that statement, it runs the query against the database... again and again and again!

  • network_noadle (unregistered)

    Another hazard is that someone manually added some indices to the database over time, and they got dropped/lost during the migration to the new platform. I've seen it happen...

  • The Beast in Black (unregistered)

    Perhaps I'm missing something, but wouldn't this be boolean rather than a count, given that it sets

    Is_BookingCorr = .RecordCount > 0
  • Jonathan (unregistered)

    SELECT EXISTS?

  • Lőrinczy, Zsigmond (github)

    Apostrophes in string-parameters (like Spezification) still can lead to syntactical errors.

  • MRAB (unregistered) in reply to Lőrinczy, Zsigmond

    Why is it even "Spezification" instead of "Specification"?

  • MM (unregistered)

    Isn’t this more of an EXISTS rather than a COUNT(*) equivalent? The return value Is_BookingCorr = .RecordCount > 0 looks to return a Boolean value rather than a count.

  • Officer Johnny Holzkopf (unregistered) in reply to Lőrinczy, Zsigmond

    Hiring non-programmers as programmers has a long tradition in Germany ("Spezification") and will lead to lots of errors despide Excellency University`s Initiatife and state funding Programm's...

  • (nodebb) in reply to MM

    Maybe Boolean logic is really quaternary: FALSE, TRUE, EXTRA_TRUE, FILE_NOT_FOUND

  • (nodebb)

    I had a nasty thought. Someone sabotaged the upgrade to make performance nosedive in order to generate management/executive level support for cleaning up the codebase; until that happened everything was "working" "well enough" that no-one was given leave to do any "optimization".

  • Lurk (unregistered)

    As far as the performance drop goes my money is on one or more indexes not being reinstated after the upgrade because they were applied in a piecemeal fashion to address particular problems over the course of many years and... they. were. not. documented.

  • (nodebb) in reply to Aitkiar

    The good thing about things that encapsulate DB operations is that they allow you to build queries dynamically without a bunch of string-mangling, or building your own library. Ideally they also decrease the number of layers of your application that have database column names hardcoded into them. I get where you're coming from, though. How do you handle these issues?

  • Matte (unregistered) in reply to MRAB

    Because German keyboard has 'z' and 'c' keys swapped.

  • Foo AKA Fooo (unregistered)

    I know I'm late, but how doesn't this allow injection? Maybe no injection of DROP TABLE, but injection of additional filters which, depending on context, can be used for data exfiltration.

  • Harry Dewulf (unregistered)

    I've seen this coding error a number of times, and it almost always arises from a "readability" feature of VB/VBA where to compose a long string, we use & _ This means you'll quite often see a string being built over multiple lines, and if you aren't used to the "live" nature of adodb connections (not to mention recordset connections within vba application) it's pretty forgivable. It should read:

    .Filter = "[CustomerID]='" & CustomerID & "'" & _ " and [Section]='" & Section & "'" & _ " and [DocumentType]='" & DocumentType & "'" & _ " and [Spezification]='" & Spezification & "'"

    This approach us routinely used to build SQL statements and filters, and if you're imitating code you've seen elsewhere, but aren't habitually a VB/VBA coder (for your sins), the error is easily made (and remedied).

  • TheCPUWizard (unregistered)

    One of the strangest of my (nearly 50 year) career - large user of SQLServer licenses upgraded to 2005. SOME of their instances slowed down by orders of magnitude. Teams worked for weeks. I was called in and stomped for a while, but started to just collect data on everything I could find out..... Long story (about 3 weeks) made short, there was a "quirk" in one specific mask run of one specific Pentium processor. This was largely (but not exclusively) sold to Dell, and used a few (but not many) models......

  • (nodebb) in reply to network_noadle

    Yeah, "missing indices" is the first thought that came to my mind. Could also be inaccurate or missing stats, so the optimiser is misinformed about how to run the queries efficiently.

Leave a comment on “Curious Queries about Performance”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article