It can very frustrating trying to help someone who is asking for help, but not giving you what you need to help them. I wanted to put together this post as a sort of FAQ on how can we help you with things like queries and schema problems. If we can’t get certain information we often can’t offer more than general guidelines – and those may or may not apply to your problem.
Query Performance
In general we do not provide support on query performance. There are just too many ways to write the same query, some are fast, some are slow. There is often no way to know short of trying them. In most cases either query is good enough and people just move on. But what if your query needs a certain response time?
If you are simply taking a query from Access builder and it is too slow, that is not good enough. Did you try ANYTHING else? Check your indexes? Anything?
Engine one off code and customization
Microsoft SQL engines (Access, SQL Server, and SQL CE to a lesser extent) will rewrite poorly written queries for you. They sometimes build indexes, temp indexes, lookup tables, cross reference lookup temp systems, all sorts of things under the hood that you may not know about.
So why don’t we do all that? Microsoft spent $1 BILLION on the company that provided that technology. Yes, that is with a B – wow. And that is part of the reason SQL Server is so large.
It has a lot of code paths that are special cased code for one off scenarios. We don’t do that because we want to stay small and lightweight, and because each of those cases you add also have to be documented, tested, and maintained. Each of those add a lot to our workload. If you have 10 special cases to 10 functions you now have 100 combinations to test. Start chaining that out through all the clauses and you end up with huge complexity for test purposes.
Special Cases?
Does that mean we will never special case code? No. We will do it, but only with really good evidence that it is going to impact a lot of use cases. How do we build those use cases? Use cases is where you can really help us. We have a lot of usage scenarios based upon standard ADO.NET patterns and practices. Then we have quite a few from our own applications we built that broke those patterns. Add to that problem reports from users, and you start to get a pretty good comprehensive coverage of SQL patterns.
Entity Framework SQL patterns are totally different
Entity Framework is one use case that recently broke a lot of scenarios for us. The short lived pattern of EF questions, and the way they are structured perform very poorly with our engine because they are so far out of our normal patterns. We have a pretty good sampling of these now and we will work on them in a future release to incorporate what we learned.
Submit Use Cases
We are more than happy to accept use cases from users to incorporate into our testing. As we go through and optimize areas of the code we try to look at all the scenarios we have and ensure we cover the majority of them.
Now if you have a statement with 10 inner joins mixing outer joins across tables that will probably never make it into the mainstream test pool because it is just not a common operation. Likewise, people who write up contrived scenarios that are possible in SQL Server and not us will not be included. There must be valid reasons for a test. Just because a consultant knew some whizz bang way to get SQL Server to do something does not mean we are going to implement that one off scenario from SQL Server code.
What to include
A good use case should include all of the following information:
Database Schema
We have routines to full any schema with random data for test purposes. But before we can do that we need to see actual schema.
- Schema
- Fields like TEXT and IMAGE should also include an expected average size and datatype being stored
- Nullable fields should include % of expected NULL entries
- Any text based fields with Locale specific settings should be included
- Indexes
- Relationships
- Triggers
- Typical number of rows per table for an average user
- Extreme number of rows for a power user (if applicable to your product)
- Database Page size and Locale
Queries
The queries themselves are of course more difficult in some ways. Often a slight rewrite of the query will result in radical performance differences. Each SQL query that is important should be split into a separate item for test purposes. If queries must run as a combination to answer a single business logic question they should be grouped together to demonstrate the application use scenario.
- SQL Query
- Number of times this query is run in an average session
- Number of expected rows returned on average
- Relative importance of this query to your application ( a scale of 1-10 is often all that is required here)
- Business Purpose of this query – it is part of a chain of queries?
- Are there interactive or free form query elements? (like search in an application)
- Stored Procedures
- Why is this code in a stored procedure?
- Could this logic be duplicated in C#/VB.Net?
- Business purpose of the procedure
- Number of times called in an average session
- Relative importance of this query to your application
Application Information
What is the expected runtime for your application? Does it run on multi processor machines, or does your application only ever run a single query?
- Application Purpose
- Target Specs
- Memory usage
- Diskspace
- .Net runtime
- Threading internal to application
- Number of users
- Caching strategy – is any data cached in your app to prevent frequent lookups?
- Special application needs
Submitting your information
I think we will need to create a form to allow users to submit this type of information. Should it be a web form you fill in, or some type of desktop app that can analyze your schema and ask you questions? What do you think?
As always we appreciate your feedback as we continue to make VistaDB better and more robust for your needs.
Subscribe