# US 8,543,554 B1 · Method and system for transparent database query caching > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 8,543,554 B1 - Document ID: US8543554B1 - Status: Granted - Kind: B1 - Application number: 12/853,649 - Filed: 2010-08-10 - Granted: 2013-09-24 - Earliest claimed: 2010-08-10 - Assignees: ScalArc Inc. - Applicants: Varun Singh; Uday V. Sawanat; Prateek Goel; Naresh G. Deshaveni - Inventors: Varun Singh; Uday V. Sawanat; Prateek Goel; Naresh G. Deshaveni - Web page: https://patentgrep.com/patent/US8543554B1 ## CPC - G06F 16/24552: {Database cache management} - G06F 16/2471: {Distributed queries} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Roughly described, a database accelerator is installed in a network having client systems which makes database queries to a particular IP address and port, and database servers which accept queries received at an IP address and port. The accelerator includes a cache, and is arranged to receive, through a network port, a database query made by a client system and directed to the particular IP address and port, return response data from the cache if available there, and if not, then forward the query through a network port to the database servers. Upon receipt of response data from the servers, the accelerator can retain it in its cache in addition to forwarding it back to the client. By installing the accelerator transparently in the network, no changes are required to either the client or server software in order to add query caching functionality to an existing client/server database arrangement. ## Claims ### Claim 1 Database performance enhancement apparatus, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, the apparatus comprising a cache memory, and the apparatus being arranged to: receive, through a network port, a first SQL database query made by the set of client systems and directed to the particular IP address and port combination; determine whether the first SQL database query is an SQL write query or a read query, and if the first SQL database query is a read query then also determine whether the first SQL database query is one for which valid response data is already stored in the cache memory, and: in response to determining that the first SQL database query is a write query and not a read query, forward the first SQL database query through a network port to one or more of the servers in the plurality of database servers, and in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is already stored in the cache memory, return the response data from the cache memory, and in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is not stored in the cache memory, forward the first SQL database query through a network port to one or more of the servers in the plurality of database servers. ### Claim 2 (depends on 1) Apparatus according to claim 1, wherein the cache memory contains a plurality of entries each including a hash key dependent on an SQL query in correspondence with response data previously received for that query, and wherein the step of determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, comprises the steps of: generating a first hash key from content of the first SQL database query, and further in dependence upon at least one member of the group consisting of an SQL database name to which the first SQL query is addressed, an SQL database table name, and credentials of a user requesting the query; and comparing the first hash key to one or more of the hash keys in the cache memory. ### Claim 3 (depends on 1) Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server: receive, through a network port, response data from the particular server to the first SQL database query; store the response data in the cache memory in association with a hash key generated from content of the first SQL database query; and forward the response data through a network port to the first client system. ### Claim 4 (depends on 3) Apparatus according to claim 3, further arranged to: receive, through a network port, a second SQL database query made by the set of client systems and directed to the particular IP address and port combination; determine that content of the second SQL database query matches content of the first SQL database query; and return a response from the cache memory. ### Claim 5 (depends on 1) Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server: receive, through a network port, response data from the particular server to the first SQL database query; and forward the response data through a network port to the first client system without storing the response data in the cache memory. ### Claim 6 (depends on 1) Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server: receive, through a network port, response data from the particular server to the first SQL database query; forward the response data through a network port to the first client system; compare the first SQL database query against predetermined criteria for cacheability, and in dependence upon the comparison, store the response data in the cache memory in association with an indication of the first SQL database query. ### Claim 7 (depends on 6) Apparatus according to claim 6, wherein comparing the first SQL database query against predetermined criteria for cacheability comprises evaluating a user-defined regular expression against the first SQL database query. ### Claim 8 (depends on 1) Apparatus according to claim 1, embodied in a physical enclosure separate from the client systems and the database servers. ### Claim 9 (depends on 1) Apparatus according to claim 1, wherein the network port through which the first SQL database query is received comprises a physical network port. ### Claim 10 (depends on 1) Apparatus according to claim 1, embodied in a virtual machine. ### Claim 11 (depends on 10) Apparatus according to claim 10, wherein the network port through which the first SQL database query is received comprises a virtual network port. ### Claim 12 (depends on 1) Apparatus according to claim 1, wherein a first one of the plurality of database servers comprises: a first network port at which the first server receives all SQL database queries directed to the first server; and a plurality of subsystems each having a respective different IP address, and throughout which the first server distributes workload for executing database queries received by the first server. ### Claim 13 Database performance enhancement apparatus, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, the apparatus comprising a cache memory, and the apparatus being arranged to: receive, through a network port, response data from one of the database servers to a first SQL database read query made by a first one of the client systems; forward the response through a network port to the first client system; compare the first SQL database query against predetermined criteria for cacheability; and in dependence upon the comparison, store the response data in the cache memory in association with an indication of the first SQL database read query. ### Claim 14 (depends on 13) Apparatus according to claim 13, further arranged to: receive, through a network port, a second SQL database read query made by the set of client systems and directed to the particular IP address and port combination; determine that the second SQL database read query matches the first SQL database read query; and return the response data from the cache memory. ### Claim 15 (depends on 13) Apparatus according to claim 13, further arranged to: receive, through a network port, response data from one of the database servers to a second SQL database query made by a second one of the client systems; and forward the response data through a network port to the second client system without caching the response data. ### Claim 16 (depends on 13) Apparatus according to claim 13, wherein the predetermined criteria for cacheability is programmable by a user. ### Claim 17 (depends on 16) Apparatus according to claim 16, wherein comparing the first SQL database query against predetermined criteria for cacheability comprises evaluating a user-defined regular expression against content of the first SQL database query. ### Claim 18 (depends on 13) Apparatus according to claim 13, wherein the cache memory contents are maintained in RAM, and wherein the apparatus is further arranged to maintain a backup of the cache memory contents in persistent storage. ### Claim 19 (depends on 18) Apparatus according to claim 18, further arranged to, upon a re-boot of the apparatus, re-populate at least part of the cache memory RAM in dependence upon the contents then in persistent storage. ### Claim 20 A method for database performance enhancement, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, comprising the steps of: receiving, through a network port, a first SQL database query made by the set of client systems and directed to the particular IP address and port combination; determining whether the first SQL database query is an SQL write query or an SQL read Query, and if the first SQL database query is an SQL read query then also determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, and: if the first SQL database query is a write query and not a read query, then in response to determining that the first SQL database query is a write query and not a read query, forwarding the first SQL database query through a networks ort to one or more of the servers in the plurality of database servers, and if the first SQL database query is a read query and not a write query and is one for which valid response data is already stored in the cache memory, then in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is already stored in the cache memory, returning the response data from the cache memory, and if the first SQL database query is a read query and not a write query and is one for which valid response data is not stored in the cache memory, then in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is not stored in the cache memory, forwarding the first SQL database query through a network port to one or more of the servers in the plurality of database servers. ### Claim 21 (depends on 20) A method according to claim 20, wherein the cache memory contains a plurality of entries each including a hash key dependent on an SQL query in correspondence with response data previously received for that query, and wherein the step of determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, comprises the steps of: generating a first hash key from content of the first SQL database query, and further in dependence upon at least one member of the group consisting of an SQL database name to which the first SQL query is addressed, an SQL database table name, and credentials of a user requesting the query; and comparing the first hash key to one or more of the hash keys in the cache memory. ### Claim 22 (depends on 20) A method according to claim 20, further comprising the steps of, where the first SQL database query has been forwarded to a particular server: receiving, through a network port, response data from the particular server to the first SQL database query; storing the response data in the cache memory in association with a hash key generated from content of the first SQL database query; and forwarding the response data through a network port to the first client system. ### Claim 23 (depends on 22) A method according to claim 22, further comprising the steps of: receiving, through a network port, a second SQL database query made by the set of client systems and directed to the particular IP address and port combination; determining that content of the second SQL database query matches content of the first SQL database query; and returning a response from the cache memory. ### Claim 24 (depends on 20) A method according to claim 20, wherein the first SQL database query has been forwarded to a particular server, further comprising the steps of: receiving, through a network port, response data from the particular server to the first SQL database query; and forwarding the response data through a network port to the first client system without storing the response data in the cache memory. ### Claim 25 (depends on 20) A method according to claim 20, wherein the first SQL database query has been forwarded to a particular server, further comprising the steps of: receiving, through a network port, response data from the particular server to the first SQL database query; forwarding the response data through a network port to the first client system; comparing the first SQL database query against predetermined criteria for cacheability; and in dependence upon the comparison, storing the response data in the cache memory in association with an indication of the first SQL database query. ### Claim 26 (depends on 25) A method according to claim 25, wherein the step of comparing comprises the step of evaluating a user-defined regular expression against the first SQL database query. ### Claim 27 A method for database performance enhancement, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, comprising the steps of: receiving, through a network port, response data from one of the database servers to a first SQL database query made by a first one of the client systems; forwarding the response through a network port to the first client system; comparing the first SQL database query against predetermined criteria for cacheability; and in dependence upon the comparison, storing the response data in a cache memory in association with an indication of the first SQL database query. ### Claim 28 (depends on 27) A method according to claim 27, further comprising the steps of: receiving, through a network port, second response data from one of the database servers to a second SQL database query made by a second one of the client systems; and forwarding the second response data through a network port to the second client system without caching the second response data. ### Claim 29 (depends on 27) A method according to claim 27, further comprising the steps of: receiving, through a network port, a second SQL database query made by the set of client systems and directed to the particular IP address and port combination; determining that the second SQL database query matches the first SQL database query; and in response to the step of determining, returning response data from the cache memory. ### Claim 30 (depends on 27) A method according to claim 27, further comprising the steps of: maintaining the cache memory contents in RAM; and maintaining a backup of the cache memory contents in persistent storage. ### Claim 31 (depends on 30) A method according to claim 30, further comprising the step of, in response to a re-boot, re-populating at least part of the cache memory RAM in dependence upon the contents then in persistent storage. ### Claim 32 (depends on 13) Apparatus according to claim 13, further arranged to store in the cache memory, in association with the response data, an indication of a user-programmed time-to-live applicable to that response data. ### Claim 33 (depends on 32) Apparatus according to claim 32, wherein the predetermined criteria for cacheability include a plurality of cacheability rules, and wherein each of the cacheability rules has associated therewith an indication of a time-to-live to be associated with response data cached in accordance with the respective cacheability rule. ### Claim 34 (depends on 27) A method according to claim 27, wherein the step of storing the response data in a cache memory includes storing the response data in a cache memory in further association with an indication of a user-programmed time-to-live applicable to that response data. ### Claim 35 (depends on 34) A method according to claim 34, wherein the predetermined criteria for cacheability include a plurality of cacheability rules, and wherein each of the cacheability rules has associated therewith an indication of a time-to-live to be associated with response data cached in accordance with the respective cacheability rule.