VistaDB 5.0 is 1000x Faster for Some Queries
By · CommentsMinutes to Milliseconds
We just published a second preview release of VistaDB 5.0 that includes support for paging result sets using OFFSET and FETCH as is done in SQL Server 2012 and other databases. It also slashes the execution time of some queries from minutes to milliseconds.
The release notes include comprehensive information on everything included in VistaDB 5.0 Preview 2. For this post, I want to focus on paging and performance. Before getting in OFFSET and FETCH, let’s first talk about TOP and how it relates with the ORDER BY clause.
Optimizing TOP with ORDER BY
Consider this query:
The idea is to find the ten largest orders and list them in descending order along with the name of the customer placing the order. On my machine, Preview 2 executed this query against a database containing a million orders and ten thousand customers in 193 milliseconds. The same query took over three minutes with VistaDB 4.3. Yes, Preview 2 is about 1000x faster than VistaDB 4.3 for this particular query!
Smarter Execution Plan = Faster Results
Previous versions of VistaDB used an execution strategy like this:
- JOIN the tables
- Sort the results based on the ORDER BY clause
- Take the TOP 10 rows from the sorted results
In contrast, VistaDB 5.0 considers the implications of the TOP and ORDER BY clauses in designing a query plan more like this:
- Analyze the full query and select indexes tuned to the ORDER BY clause
- JOIN the tables and find results in pre-sorted order using the selected indexes
- Once we’ve found 10 rows, we’re done
In other words, Preview 2 can take advantage of indexes such that the results it emits are pre-sorted, whereas previous versions of VistaDB would internally create and sort a temp table to process any query containing an ORDER BY clause.
TOP Speed Results
Here is some data from running a few variants of the query that simply vary the number of rows requested in the TOP clause.
The performance of Preview 2 varies linearly with the number of rows returned. The execution time for the same queries in VistaDB 4.3 is nearly constant at 3-4 minutes because execution is dominated by the time spent creating and sorting a million row temp table only to discard all but the few rows requested in the TOP clause.
OFFSET and FETCH Paging Support
Our implementation of OFFSET and FETCH builds on the fundamental improvements we’ve made to the engine. Consider this query:
This is functionally equivalent to selecting TOP(100), and has the same measured performance. As we increase the OFFSET, the execution time increases linearly because the engine still has to walk over those offset rows.
If you’d like to try our VistaDB 5.0 Preview 2, I encourage you to get the bits. If you’d like to replicate my results on your own machine, I have posted my sample database along with the queries I ran and my results.
No-Install-Necessary Rapid Loupe Implementation
By · CommentsIn case you haven’t been reading between the lines (and really, who has time for that?) We’ve enabled a complete solution for Loupe that lets you go start to finish without an installation or anything else that modifies your machine. It’s build-server friendly (because all you need is NuGet or your own copy of a few files) and invisible until you want or need it.
Only Two Steps to The Best Logging Available
How do you get there?
- Use NuGet to Add Gibraltar.Agent.Web (for a web site) or just Gibraltar.Agent for any other application.
- On your Web Site or Executable use NuGet and add Gibraltar.Loupe.Viewer. You don’t need a reference to it, you just want to have it included in your shipping application.
Local Sessions List
During development, run LoupeViewer.exe on your computer to get immediate real-time access to log data from your applications while you’re developing (no worries – it’s designed to just run in the background and let you know when there are new sessions you can access).
When your application is running in the field – be it on a server or a customer’s desktop – you can have them just start up the LoupeViewer.exe to see any Loupe-enabled application running on their machine. It can trigger log file rollover so you can copy out the log files for use with the full Loupe Desktop if you want or submit them to Loupe Server, again if you want.
Graduate to Loupe Service for a Zero Effort Team Solution
When you’re moving from development to deployment you’ll definitely want to check out Loupe Server: Customer after Customer tells us it has helped them find a number of problems happening in the field that their users never informed them of. This is the reality of applications in the field – few people will take the time to tell you that your application has messed up and it’s caused them a problem, even fewer on the web. But, each one is a potential upset user.
Here our install-less story continues – just use the configuration wizard built into the LoupeViewer to configure your application to send to a trial copy of our Loupe Software-as-a-Service offering. Free for 30 days, you can monitor what versions folks are running in the field and be well informed of any problems they run into. Of course, you don’t have to use the wizard, you can edit your configuration by hand referencing our documentation if you’d prefer.
And that’s the whole story – soup to nuts, no installation! Of course, our legendary support is available every step along the way if you’d like some assistance. Just let us know how we can help get you up and running with Loupe!
The Time has Come the Walrus Said…
By · Comments… to talk of many things: Of shoes and ships and sealing-wax, Of cabbages and kings.
And it’s the “many things” that interest me. As some of you may already know, I’m leaving Gibraltar Software to take up an opportunity to work in the field of Data Science.
You’ll have realised by now that I’m fascinated by the “many things” that we can collect and analyse these days with the help of the Internet and tools such as Hadoop, and the many questions that such collection and analysis can answer. These are exciting times in which to be a Data Scientist!
I’ve enjoyed my time with Gibraltar Software and working with some of the great guys on the team. I wish them all the best in their future endeavours and I’m sure you will continue to benefit from the great software that these guys turn out.
In the meantime, I’m sure I’ll see some of you around at some conference somewhere, until then I’ll quietly close the door behind me and wish you, happy coding! 
New Redistributable Loupe Viewer (and it’s free!)
By · CommentsI’m happy to announce that we’ve published the Loupe Viewer – available immediately via NuGet and included in the upcoming Loupe 3.5.1 update. This free utility satisfies a set of goals we’ve had for a while with Loupe:
- Make Loupe More Useful to Coders - We all get that logs are great when diagnosing a problem once it’s in production, but the best way to have great logs is to have them be useful during development when you’re rapidly iterating on your local machine.
- Provide a Redistributable Viewer - When you’re RDP’d into that production box you want a lightweight, reliable way to view what’s going on in real time. It has to be dead simple to deploy and work without messing around with the sites & services on the computer.
We’ve hit the mark with Loupe Viewer:
- It’s a single file, just over 2 MB.
- You can start it on a computer and within seconds see a list of every Loupe-enabled application running on that computer. Just double-click one to start viewing the log in real time (starting with the last thousand log messages)
- It includes everything you need to get started with Loupe – the configuration wizard, Agents.. Even a link to the getting started videos. So you don’t have to install anything to go from zero to fully running with Loupe.
Getting Started with Loupe Viewer
Local Sessions List
We recommend you add Loupe Viewer to your application or web site from NuGet. This way you’ll get the latest version included in your application. On your development machine you’ll want to copy the viewer to somewhere common and run it from there. When you start the Loupe Viewer you’ll see a list of all of the Loupe-enabled applications running on your computer. In this case, I fired it up on one of our test servers so you can see the Loupe Server web site, Windows Service, and Admin tool running.
For your convenience you’ll notice that you can also Add Loupe to an application (this edits the app.config/web.config and adds references to the Agents) and view getting started videos right from the viewer.
Viewing Live Data
View Log Message Details in Real Time
When you want to look into a session you can just double click its entry to start a live view session. You’ll notice that we’ve enhanced the Live Viewer to add the user name and thread to the display by default. The live view communicates with the process you’re monitoring through a local TCP socket that won’t be blocked by a firewall or require a working LAN connection. For efficiency, the full log message data is only sent out when there’s an active live view so you can leave the Local Sessions window running indefinitely without any concern for resource usage on your computer.
If you click on a message the grid will pause updates so even if your server is quite busy you won’t have to worry about the information you’re viewing scrolling off the screen. It’ll buffer up to 10,000 messages so when you’re ready to catch up to real time just click Play in the upper left and presto, you’re at the tail of the log.
There are a number of other features in the live viewer including severity filters, a search box, even the ability to see related source code if it’s available on the computer.
Severity Filters, Search, and More
Send Data to Loupe Server Right Now
If you’ve configured the application you’re viewing to send data to Loupe Server then you can cause the application to send data immediately by clicking Save. This also rolls over the log file so you can use the full Loupe Desktop to dig into the entire session data set if you want. If you need to transfer the log files to another computer you can find them sitting in the local log directory (typically C:\ProgramData\Gibraltar\Local Logs\<Your Product Name>).
Application Start Notification
Every time a Loupe-enabled application starts and Loupe Viewer is running in the background it’ll display a desktop notification to let you know that there’s a new session you can view.
This is particularly handy when developing as it makes it easy to start the live view for the current debugging session when you start a fresh copy of an application. But, if you find it distracting you can always disable it from the context menu.
