Skip to content

2014🔗

  • 3 min read

Dev Tools: XYplorer (review 1) - Catalog

currently on version 14.60 I'm a big fan of finding tools that help automate and streamline things that should are routine actions.Surprisingly, I've found it incredibly challenging to move away from the default Windows Explorer for file management, as the familiarity it offers makes it somewhat tough to be patient with learning an alternative, especially if the alternative offers more complication. That's where I stood with XYPlorer for sometime. It is a developer's tool first and foremost. It is complicated, but with this complication comes an extremely robust set of features. I honestly think that I'll never really know all of them, as this is more than just a swiss knife for file management.

This is almost like stepping from a text editor for editing code to a full blown visual studio IDE. There is just that that much to learn! Over time, I'm finding myself less frustrated by using it, and more amazed at the tweaks here and there that can be found that can greatly enhance one's file management and workflow, personal and professional. I won't cover all features, but I think instead of doing a full blown review on the product, I'm going to add some incremental reviews on features as I discover, otherwise the vast feature-set will end up causing nothing but writer's block and I'll never share anything (cause I'll be busy learning)

Catalog
  • Replaces Favorites with additional functionality The favorites section is one of my most used features in explorer. I setup the default locations I'm commonly navigating to, such as my SQL Query files location, cloud drives, temporary projects I'm working on, appdata folders I need access to occasionally, and more. XYPlorer Expands on this greatly by the concept of Catalogs. Instead of just having a shortcut, Catalogs allows one to expand the concept of shortcuts far beyond Windows Explorer (hereafter referred to as WE) and combines the favorites functionality with much more features.

    The Tree is an image of your computer's file system. It shows you all what's there. But, most of the time all is just too much... The Catalog is the answer: here you can grow your own personal tree. Your favorite locations are deep down in some heavily nested structures? Lift them to the surface! Side by side with locations from the other end of your hard disk. You can navigate by the Catalog (finally a one-click favorite solution!) and you can drop onto the Catalog's items. XYPlorer Help The catalog houses many categories. Each of these categories can provide various functionality beyond just linking to favorites.

XYPlorer_Catalog_1

Here you can see applications listed directly. They provide functionality to open the app, open a file you drag onto it with the app (bypassing need to use "open with" dialogue)

XYPlorer_Catalog_2

Opening the properties of a file allow one to futher edit the actions the application performs.

XYPlorer_Catalog_3 .... to be continued.

Lots of functionality in the catalog to benefit from, but time is limited, I'm going to visit further in next post.

note: was given a license by developer to help me evaluate long term. This did not affect my review, as it wasn't solicited at all by XYPlorer developer.

  • 3 min read

Tl;dr

(time constraints prevented me from reworking significantly)

"or-condition-performance" article on SQL Server Pro was forwarded over to me to research by a friend who indicated that using a variable with an or pattern had historically caused table scans. This was a suprise to me as all previous queries with optional parameters I'd used in the past seemed to use index seeks. I had to dig into this a little deeper to see if had been missing this in my past work and needed to find an alternative method for optional parameters. Original test procedure copied from original.

The result from running the test1 procedure was to find a clustered index scan. SQL Server optimizer should be able to utilize the or conditions as long as an index covers the predicates, so I dug in deeper. When I ran a random query against a few tables I found this was creating table scans. Looking a little deeper I decided to evaluate the indexing on the tables to see if it was an issues with indexing, and not the pattern of using the OR against a variable.

Test 1: SCANS - except with option recompile Found index scans on all my versions of running this except when including option(recompile) inside the stored procedure statement text. This of course fixed the issue by allowing sql to build the plan based on the exact value passed in, however, this would be at the cost of increasing CPU and negating the benefits of having a cached plan ready. Test 2: Ran exec sys.sp_updatestats

Updating [dbo].[or_test]     [PK__or_test__3213E83F7953A2B2], update is not necessary...     [idx_or_test_col1], update is not necessary...     [idx_or_test_col2], update is not necessary...     [ix_nc_CoveringIndex], update is not necessary...     0 index(es)/statistic(s) have been updated, 4 did not require update.

After researching for hours more, and reading many posts, I discovered I've been missing this in previous work, probably due to query plan caching. When utilizing the variable from the stored procedure, the parameters are "sniffed". This means the plan is not rebuilt for each execution, but instead first execution is cached with the values it utilized. Thereafter, the optimizer can reuse this plan. The difference is that if you provide a value you manually plug into your test such as "declare @Value = 'FOO' " then the optimizer has an actual value to use for each of your manually run executions. This means that if you have properly indexed the column, it would be sargable.

However, the stored procedure is not passing in the actual value after the first run, it is trying to save the CPU demand the optimizer will need, and instead use the cached plan. This is likely the cause of my missing this in the past, as all my execution testing was based on commenting out the stored proc header and running manual tests. In this case, I'd correctly be seeing table seeks if indexed properly, because the optimizer was obtaining the actual values from my session. When executing the stored procedure externally, it looks to utilize parameter sniffing, which when working correctly is a good thing not a bug.

However, when result sets can greatly vary, the problem of parameter sniffing can become a problem. In addition, if the OR statement is utilized as my original problem mentioned, the optimizer can decide that since parameter value is unknown at this time, that with an OR clause it would be better to run a table scan since all table results might be returned, rather than a seek.

To bypass this, there are various approaches, but all are a compromise of some sort. The common approach to resolving if truly various selections may be made (in the case of SSRS reports for example) is to utilize option(recompile). This provide the actual value back to the compiler at run time, causing higher CPU usage, but also ensuring the usage of indexes and reducing scans when the columns are properly indexed. Again, this is one solution among several, which can include building dynamic query strings, logic blocks, and other methods.

  • 1 min read

Get Information on Current Traces Running

This is just a quick informational query to save as a snippet to get some quick information on running traces, and provide the stop and close snippet quickly if you want to stop a server side trace you created.

  • 1 min read

Generate Random Date With Starting Point

If you want to create sample random samples when dealing with date calculations to test your results, you can easily create a start and end point of randomly created dates. This is a snippet I've saved for reuse:

DATEADD(day, (ABS(CHECKSUM(NEWID())) % $Days Seed Value$), '$MinDate$')

This should let you set the starting point (Min Date) and choose how far you want to go up from there as a the seed value. Ie, starting point 1/1/2014 with seed of 60 will create random dates up to 60 days outside the min date specified. Stackoverflow Original Discussion: How to update rows with a random date asked Apr 27 '09

  • 1 min read

Eliminate Overlapping Dates

I was looking for an efficient way to eliminate overlapping days when provided with a historical table that provided events that could overlap. In my case, I had dates show the range of a process. However, the multiple start and end dates could overlap, and even run concurrently. To eliminate double counting the days the process truly was in play I needed a way to find eliminate the overlap, and eliminate duplicate days when running in parallel. I researched ways to complete this and found the solution through this post. Solutions to Packing Date and Time Intervals Puzzle

Itzik provided an excellent solution, though I had to take time to digest. The only problem I ran into, was his solution was focused on a single user and dates. For my purposes, I need to evaluate an account and further break it down by overlap on a particular process. Grateful for SQL MVP's contributions to the community as this was a brain bender!

Example of Problem and Solution with Overlapping Dates

  • 1 min read

Finding Groups - Consecutive Months

A step by step explanation on one way to get a consecutive period of months, which could easily be adapted to days, years, or other values. I'll continue on this track and post a tutorial on eliminating overlapping dates soon.

  • 1 min read

Scalar functions can be the hidden boogie man

Ran across a comment the other day that scalar functions prohibit parallelism for a query when included. I thought it would be worth taking a look, but didn't take it 100% seriously. Came across the same indication today when reviewing MVP deep dives, so I put it to the test.Turns out even a simple select with a dateadd in a scalar format was affected enough with that one action to drop 5% on the execution plan. When dealing with merge or other processes that would benefit from parallelism, this would become even more pronounced. Suggest reading "Death by UDF" section by Kevin Boles. This comment is buried at the very end of the chapter. He indicates

"One final parting gift: scalar UDFs also void the use of parallel query plans, which is why the FormatDate UDFpegged only ONE CPU core on my laptop! " (Page 194-summary)