Phone800.366.3472 SupportGet Support DocumentationDocumentation Resource CenterResource Center
search
close
Open Menu

Synergex Blog


Presenting in Synergy v11.1.1 Select.Where.Like – You Should Like This

By Phil Bratt, Posted on October 21, 2019 at 9:09 am

Avatar

If you’ve ever been coached or trained in public speaking, you’ve probably been made painfully aware of your use of the dreaded filler words—words you say almost automatically when speaking and nervous. Everyone has their own flavor of it, but for me, it’s the word “like.” If I’m really nervous, I can end up sounding like Moon Unit Zappa in her single Valley Girl. (Millennials, ask your parents. Gen Z, ask your grandparents.)

But the word “like” gets a bad rap and in some uses is fine and perfectly acceptable, like making a comparison or at the beginning of a list talking about the uses of like. Today, though, I’m talking about like in the context of new features added to Synergy/DE in version 11. Which introduces the new system-supplied class Select.Where.Like.

If you aren’t familiar with the Select class in Synergy, where have you been? We’ve been harping on about this thing, like, forever. It’s a wonderful feature in the language for reading data in a SQL-like way. Well, the Select class now has even more functionality with the addition of the Like statement, allowing you to filter your alpha results based on a matching pattern. It’s similar to Where.Contains in that you can do the equivalent of Where.Contains with Where.Like, plus so much more. A Where.Contains is a Where.Like that has a “%” sign in the front and back, telling it to search the results for that string of characters anywhere in the field.

How does Select.Where.Like work?

MatchWildcardExample
Zero or more characters%bl% finds bl, black, blue, and blob
Single character_h_t finds hot, hat, and hit
Single character in a set[]h[oa]t finds hot and hat but not hit
Single character not in a set[^]h[^oa]t finds hit but not hot and hat
Single character in a range[-]c[a-c]t finds cat, cbt, and cc but not cot

Using the above wildcards, you can build your Where statement to look for a pattern inside a record field. For example, say you have a city address in the form CityName, ST(ate). Now let’s say you want all results from entries from CA where the city starts with “SAN”. What would this look like?

Where.Like(city_name,"San%,CA%")

Breaking this down, the first three characters are “San”, then there are one or more characters between “San” and our next part “,CA”, which can have one or more characters after it. This query should return results like San Francisco, San Diego, Santa Monica, and San Jose.

Note that you can do a Where.Like that is not case sensitive with NoCaseWhere.Like. The above statement wouldn’t find a city if “san” was in the body of the city name, like Pleasanton, CA. This is most helpful if your data is not uniform or consistent (like sometimes the state is ca or cA). To get the most use out of Where.Like, you should be familiar with your dataset and how information is entered and modified.

Getting an idea how this works

Let’s try something a little tougher. Let’s say you have data that contains postal codes, but the postal codes can be from different countries. Now you could combine your Where.Like with an AND to a field that has a country, but you could also handle it all within the Where.Like. For instance, let’s say the data has postal codes from three different countries: United States, United Kingdom, and Sweden. An example of each one would be

US: 99999
Sweden: 999 999
UK: varies but most commonly AA9 9AA

where 9 is a number and A is a letter.

If we wanted to be completely explicit, we could use these Where.Like statements:

US: [0-9][0-9][0-9][0-9][0-9] 
Sweden: [0-9][0-9][0-9] [0-9][0-9][0-9]
UK: [A-Z][A-Z][0-9] [0-9][A-Z][A-Z]

This is a manual way to handle it, but maybe we can do it with less. If we want only US and Sweden, we can use the fact that UK postal codes have letters to our advantage and go with a simple

US & Sweden: [^A-Z]%

This essentially equates to any postal code that doesn’t begin with a letter, which works for our simplified version of these countries’ postal codes. The opposite, [A-Z]%, also works if you want UK only.

If we want to do Sweden and the UK, we note that both contain a space in their postal codes, so

UK & Sweden: %[ ]%

This includes any postal code with a space in it. Now this is tricky, as these could be Synergy alpha types, and there might be trailing spaces at the end. To fix this scenario, we use a wildcard to say there is a number or letter after the space:

UK & Sweden: %[ ][0-9]%

This means that we are looking for a space with a number immediately following. However, unlike the last example, we can’t use the opposite of this to get US only, because %[^ ][^0-9]% actually works for all three countries if there are trailing spaces; a letter or number at the end followed by a non-numeric character works. For example, a UK postal code ending in “G” with trailing spaces satisfies this condition.

What’s unique about US postal codes, though, is that there are a series of four numbers in a row, and they have fewer total characters than the other two. Therefore, %[0-9][0-9][0-9][0-9]% would work, as would _ _ _ _ _  % (i.e., five wildcard spaces followed by a literal “ ”), as there would be a space afterwards if this were an alpha field, whereas it would be a numeric or alpha character in the UK or Swedish postal codes. Of course, this would be quite different in practice, because your searches would look more like “98_ [0-9]%” if you were looking for Swedish postal codes starting with 98. But the purpose here is to get you thinking with wildcards. This new feature has a lot of potential to optimize searches as it does when used inside of SQL. Be sure to try it out now in Synergy 11 and read more about this feature in our online documentation.


Performance troubleshooting

By Phil Bratt, Posted on May 14, 2018 at 10:22 am

Avatar

In the 1984 movie classic Ghostbusters, we are introduced to Bill Murray’s character Dr. Peter Venkman, a professor of paranormal studies, testing subjects for the gift of clairvoyance—the ability to gain information about an object, person, location, or event through extrasensory perception. While it is clear Dr. Venkman does not take such things very seriously, we can see the advantage of such an ability, particularly for developers.

Stop me if you’ve heard this before: “We upgraded X and now Y is happening.” In this case, Y is usually associated with a negative behavior like slow performance, a slew of errors, or a bad user experience. Events like these may induce weariness, nausea, dry mouth, and other various side effects that are usually listed in American pharmaceutical commercials. In summary, they’re unpleasant. If only there were some means to predict these events and avoid them in the future…

Unfortunately, most developers are not gifted with the power of clairvoyance to anticipate problems with upgrades before they happen. But maybe instead of a crystal ball, there are tools that can help us avoid upgrade failure. Let’s look at some things that can help anticipate and/or prevent such issues from happening.

A relatively recent addition to the Synergy documentation is the Configuring for performance and resiliency topic in the Installation Configuration Guide. This topic discusses things that one should take into consideration when running Synergy on any system, and it’s based on years of experience from the Synergex Development staff and the results of their testing. If you haven’t read this section yet, I highly recommend doing so. If you’ve already read it, I recommend a quick refresher the next time you’re looking at major system or software changes.

In Support, we often see issues when developers virtualize systems that run Synergy or when data is migrated and then accessed across a network rather than being stored locally. Both scenarios are discussed in this topic. And as part of Synergy’s web-based documentation set, it’s updated regularly with the latest information. Make sure you take a look before any major upgrade in case there are changes.

Other useful tools for avoiding problems are the Synergex Blog, the Synergy migration guides, KnowledgeBase articles like Guideline for debugging performance issues, and the release notes that come with each version of Synergy. Remember that even if you are just changing the operating system and/or hardware and not your version of Synergy, you should re-review these materials. Some of the considerations they outline may now be relevant, even if they didn’t affect you previously. Also, when testing, remember to take load testing or a process running over time into account. We commonly see pitfalls  when developers neglect these two factors in testing.

Taking Action

Now let’s say that despite your excellent planning, you do see a performance issue. What can you do? Here are some steps I’ve found helpful that might get overlooked. Most have to do with simply eliminating factors that affect performance.

  • Establish a baseline

If you’re going to diagnose a problem in performance, the first thing to do is isolate code or create a piece of code that demonstrates the problem. Make this your baseline test for all of the various configurations you’re going to test. This will make your tests consistent as well as eliminate code changes as a factor.

  • Use a metric

Establish a program you’re going to use to measure the difference in performance. If you’re using a traditional Synergy program as your baseline, you can use the Synergy DBL Profiler, which will count CPU time for you. Just make sure you pick the same metric for your testing—CPU time is not the same as real time. This step will enable you to get measurable results to test what is actually making a difference.

  • One by one, test all the things

I’ve found that the easiest way to plan and visualize testing is to make a tree. Each layer is one aspect you’re testing that continues to branch with every different aspect. For example, I had a situation where a production machine migrated Synergy version, operating system, and hardware and virtualized the OS, all in one move. We picked one thing to change (the virtualization of the OS) and tested it.

VirtualizedNon-Virtualized

 

By doing this, we established that virtualization was a factor, because a virtualized environment was slower than a non-virtualized one. We then compared those to the old and new Windows versions, but continued with virtualized and non-virtualized environments using the same virtualization software.

Windows 8Windows 10
VirtualizedNon-VirtualizedVirtualizedNon-Virtualized
In previous tableIn previous table

 

On average, this produced the same result. (It was I/O processing, so we did an average of 10-20 runs based on how volatile the results could be.) Next, we compared the Synergy 10 runtime with the Synergy 9 one.

Windows 8Windows 10
VirtualizedNon-VirtualizedVirtualizedNon-Virtualized
Syn 9Syn 10Syn9Syn 10Syn 9Syn10Syn 9Syn 10
In previousIn previousIn previousIn previous

 

The tree continued growing until all of the factors were considered and tested.

Closing Thoughts

It can be tedious to test one change at a time, but without that kind of granularity, you can’t establish which change affected performance and by how much. In the example I mentioned above, we established that virtualizing the hardware was causing a problem because of the way the virtual machine software emulated separate cores. We never would have come to such a conclusion without carefully eliminating the many different changes one at a time.

After you’re able to establish exactly which changes caused the performance issue(s) and by how much, you can work on a fix or provide a solid case to whichever software support representative you need to contact to get a fix.

You might know most of this already. You might even know of some methods, tips, etc., for performance issues that I didn’t discuss. Maybe you are clairvoyant and you already knew the contents of this post before I did. Either way, I hope you find this information helpful when you look at performance in the future, in both preventative measures and problem diagnosis.


Don't miss a post!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Recent Posts Categories Tag Cloud Archives