SQL

Syndicate content 29 

Have some free text fields in your application that you wish you could search efficiently?  Tried using some methods before but found out that they just cannot match the performance needs of your customers?  Do I have one weird trick that will solve all your problems?  Don’t you already know!?  All I do is bring great solutions to your performance pitfalls!

As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end.  Just know you are hurting my feelings.

Last comment 10 May 2019
+ 19   1 3
1278

views

+ 19

rating

Date range queries going too slow for you?  SQL Performance got you down?  I have one weird trick that might just help you out! (SQL Developers hate this!)*

If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp< TimeStampif and only if ID1 < IDfor all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges.  Consider the following table:

Last comment 4 June 2018
+ 14   1 9
5643

views

+ 14

rating

Class Queries in InterSystems Caché is a useful tool that separates SQL queries from Caché Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

Last comment 9 December 2016
+ 14   0 9
3932

views

+ 14

rating

Introduction

The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.

Last comment 15 June 2016
+ 13   0 11
1865

views

+ 13

rating

I have finished my 4th book about Caché and MUMPS. This will probably be my last.

I am deeply grateful and humbled for all the help I have received from this group and the WW Response Center.

You all have something very special going here. 

Last comment 10 August 2018
+ 12   0 1
686

views

+ 12

rating

Last week, we announced the InterSystems IRIS Data Platform, our new and comprehensive platform for all your data endeavours, whether transactional, analytics or both. We've included many of the features our customers know and loved from Caché and Ensemble, but in this article we'll shed a little more light on one of the new capabilities of the platform: SQL Sharding, a powerful new feature in our scalability story.

Last comment 31 January 2018
+ 12   0 8
902

views

+ 12

rating

As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.

Last comment 30 May 2017
+ 11   0 12
2591

views

+ 11

rating

The Art of Mapping Globals to Classes  (2 of 3)

 

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

 

This example is going to cram in 4 or 5 different things beyond what was covered in Part 1 

Last comment 23 May 2017
+ 10   0 6
1180

views

+ 10

rating

This post is the direct result of working with an InterSystems customer who came to me with the following problem:

SELECT COUNT(*) FROM MyCustomTable

Takes 0.005 seconds, total 2300 rows.  However:

Last comment 1 November 2016
+ 10   0 11
984

views

+ 10

rating

The object and relational data models of the Caché database support three types of indexes, which are standard, bitmap, and bitslice. In addition to these three native types, developers can declare their own custom types of indexes and use them in any classes since version 2013.1. For example, iFind text indexes use that mechanism.

Last comment 29 January 2016
+ 10   0 8
1241

views

+ 10

rating

Order is a necessity for everyone, but not everyone understands it in the same way
(Fausto Cercignani)

Disclaimer: This article uses Russian language and Cyrillic alphabet as examples, but is relevant for anyone who uses Caché in a non-English locale.
Please note that this article refers mostly to NLS collations, which are different than SQL collations. SQL collations (such as SQLUPPER, SQLSTRING, EXACT which means no collation, TRUNCATE, etc.) are actual functions that are explicitly applied to some values, and whose results are sometimes explicitly stored in the global subscripts. When stored in subscripts, these values would naturally follow the NLS collation in effect (“SQL and NLS Collations”).

Last comment 10 June 2016
+ 9   0 0
1626

views

+ 9

rating

As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.

Last comment 28 December 2018
+ 8   2 4
1924

views

+ 8

rating

The Art of Mapping Globals to Classes (3 of 3)

 

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

 

If the above does not sound familiar to you please start at the beginning with the following:

 

The Art of Mapping Globals 1

Last comment 2 November 2017
+ 8   0 7
867

views

+ 8

rating

There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

  1. The types of indices available
  2. Using one index type over another
  3. The information TuneTable gathers for a table and what it means to the Optimizer
  4. How to read a Show Plan to better understand if a query is good or bad
Last comment 18 January 2019
+ 7   3 2
360

views

+ 7

rating

No doubt bitmap indexing, if used with a suitable property, performs just impressive!
But there is a major limit: ID key has to be a positive integer.
For modern class definitions working with CacheStorage this is a default.

BUT: There are hundreds (thousands ?) old applications out in the field that
are still using composite ID keys.
Or - to name the origin - work on Globals with 2 subscript levels (or more).
They are by construction excluded from our "Bitmap Wonderland".

Last comment 17 February 2018
+ 7   1 0
391

views

+ 7

rating

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

Last comment 18 October 2017
+ 7   1 12
1292

views

+ 7

rating

So I know it's been a while, and I hate to let my adoring fans down... just not enough to actually start writing again.  But the wait is over and I'm back!  Now bask in my beautiful ginger words!

For this series, I am going to look at some common problems we see in the WRC and discuss some common solutions.  Of course, even if you find a solution here, you are always welcome to call in and expression you gratitude, or just hear my voice!

This week's common problem: "My query returns no data."

Last comment 16 January 2017
+ 7   0 2
289

views

+ 7

rating

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Last comment 13 July 2016
+ 7   0 9
2922

views

+ 7

rating

Earlier in this series, we've presented four different demo applications for iKnow, illustrating how its unique bottom-up approach allows users to explore the concepts and context of their unstructured data and then leverage these insights to implement real-world use cases. We started small and simple with core exploration through the Knowledge Portal, then organized our records according to content with the Set Analysis Demoorganized our domain knowledge using the Dictionary Builder Demo and finally build complex rules to extract nontrivial patterns from text with the Rules Builder Demo.

This time, we'll dive into a different area of the iKnow feature set: iFind. Where iKnow's core APIs are all about exploration and leveraging those results programmatically in applications and analytics, iFind is focused specifically on search scenarios in a pure SQL context. We'll be presenting a simple search portal implemented in Zen that showcases iFind's main features.

Last comment 28 June 2016
+ 7   0 6
575

views

+ 7

rating

I am pleased to announce that Release Candidates of Caché and Ensemble 2017.2 are now available.

Many customers have already downloaded the Field Test over the past few months, and we appreciate your feedback.

This release contains significant improvements, including:

Last comment 10 October 2017
+ 6   0 2
727

views

+ 6

rating

Quotes (1NF/2NF/3NF)ru:

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be
  • atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
  • non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)

This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".

+ 6   0 3
0

comments

393

views

+ 6

rating