Picasso Database Query Optimizer Visualizer

©Indian Institute of Science, Bangalore, India

 

COMMAND LINE INTERFACE to PICASSO SERVER

 

Apart from the Picasso Client graphical interface described in the main documentation, the Picasso Server can also be accessed directly through the command line. The utility resides in the PicassoRun directory and is called PicassoCmd.

 

The command line utility can be used as follows:

  1. a) For generating exact diagrams that have the same resolution and the complete range on all dimensions:

    PicassoCmd  <ServerName> <Port> <DBConnection> <OptLevel> <QTD> <QDist> <DiagType> <QTFile> <Resolution>

    b) For generating approximate diagrams that have the same resolution and the complete range on all dimensions:

    PicassoCmd  <ServerName> <Port> <DBConnection> <OptLevel> <QTD> <QDist> <DiagType> <QTFile> <Approx-algo> <IdError> <LocError> <Resolution>

     

  2. a) For generating exact diagrams with dimension-specific ranges and/or resolutions:

    PicassoCmd  -R <ServerName> <Port> <DBConnection> <OptLevel> <QTD> <QDist> <DiagType> <QTFile> <Approx-algo> <IdError> <LocError> {<Resolution> <Startpoint> <Endpoint>}


    b) For generating approximate diagrams with dimension-specific ranges and/or resolutions:

    PicassoCmd  -R <ServerName> <Port> <DBConnection> <OptLevel> <QTD> <QDist> <DiagType> <QTFile> {<Resolution> <Startpoint> <Endpoint>}


 

The number of  {<Resolution> <Startpoint> <Endpoint>} triplets has to be identical to the dimensionality of the query template. The triplets are associated with  the PSP predicates in their syntactic order of appearance in the query template – i.e., the first triplet corresponds to the first  :varies predicate in textual order, the second triplet corresponds to the second :varies predicate, and so on.

 

 

The following table explains the various arguments:

Argument

Meaning

ServerName

Name/IP of machine running Picasso Server

Port

Port number of Picasso Server

DBConnection

Database Connection Descriptor

OptLevel

Database Engine's Optimization Level

QTD

Query Template Descriptor

QDist

Query Distribution (permitted values are Uniform and Exponential)

DiagType

Diagram Type (permitted values are Compilation, Approximate and Execution)

QTFile

File containing the Query Template

Approx-algo

Choice of Approximation algorithm (permitted values are Sampling and Grid)

IdError

Identity error tolerance (1-99 percent)

LocError

Location error tolerance (1-99 percent)

Resolution

Number of query points along a dimension (permitted values are 10, 30, 100, 300 and 1000)

Startpoint

Start point of the selectivity space along the dimension (0-99 percent)

Endpoint

End point of the selectivity space along the dimension (1-100 percent).  Endpoint must be greater than Startpoint by at least 1%.

   

 Notes:

1. The first eight arguments and  <Resolution> are compulsory for all diagrams. Further, if DiagType = Approximate, then the arguments <Approx-algo>, <IdError>, <LocError> are compulsory.

2. If the -R option is used, then the number of {<Resolution> <startPoint> <endPoint>} triplets must be exactly the same as the dimensionality of the query template.

 

 

The following are representative examples of using the command line facility:

 

  1. PicassoCmd    localhost   4444   sql_localhost   default  SQL_Default_Uniform_100_q2  Uniform  Compilation  E:\Picasso\QueryTemplates\sqlserver\q2.sql 100

first connects to the Picasso server residing on “localhost” at port “4444” and then to the database engine using the “sql_localhost” connection descriptor. It then produces compilation diagrams with QTD “SQL_Default_Uniform_100_q2” at a “default” optimization level with queries Uniformly distributed at a resolution of 100 on each dimension over the entire selectivity range, for the query template in file “E:\Picasso\QueryTemplates\sqlserver\q2.sql”.

 

  1. PicassoCmd    localhost   4444   sql_localhost   default  SQL_Default_Uniform_100_q8_RS-NN  Uniform  Approximate E:\Picasso\QueryTemplates\sqlserver\q8.sql Sampling 10 20 100

first connects to the Picasso server residing on “localhost” at port “4444” and then to the database engine using the “sql_localhost” connection descriptor. It then produces approximate diagrams with QTD “SQL_Default_Uniform_100_q8_RS-NN” at a “default” optimization level with queries Uniformly distributed at a resolution of 100 on each dimension over the entire selectivity range, for the query template in file “E:\Picasso\QueryTemplates\sqlserver\q2.sql” using the "Sampling (RS-NN)" approximation algorithm with tolerances of 10% identity error and 20% location error.

 

  1. PicassoCmd  -R  localhost   4444   sql_localhost   default  SQL_Default_Uniform_100_q9_custom  Uniform  Compilation E:\Picasso\QueryTemplates\sqlserver\q8.sql 100 10 80 30 20 50

first connects to the Picasso server residing on “localhost” at port “4444” and then to the database engine using the “sql_localhost” connection descriptor. It then produces compilation diagrams with QTD “SQL_Default_Uniform_100_q9_custom” at a “default” optimization level with queries Uniformly distributed for the query template in file “E:\Picasso\QueryTemplates\sqlserver\q9.sql”. The diagram has customized range and resolution on different dimensions – in the first dimension, the resolution is 100 with the selectivity range going from 10% to 80%, while in the second dimension the resolution is 30 with the selectivity range going from 20% to 50%.

 

 

Documentation Home