13

Some days ago I discovered the Stack Exchange Data Explorer (SEDE) which provides already lots of interesting queries out of the box (thanks to the contributors).

After a while I noticed that some of the queries are running very long. In fact, they ran so long that they were cancelled by the system, which showed the following message:

Something unexpected went wrong while running your query. Don't worry, blame is already being assigned.

One example is the Top Users by Country list. Well, I thought this isn't really a big issue, because I can (try to) enhance the query myself. So I opened the query composer, saw the SQL text field on the left and the database schema on the right. A quick search returned some additional descriptions of the tables the columns.

But what I missed was a description of indexes and foreign keys, because they have a big influence on how I build a query and they are quite essential for query optimization.

So my questions: can I find this information somewhere? Or even better: could this be added to the SEDE? I'd appreciate it as well, if I could evaluate an execution plan without running the query. Would that be possible?

p.s.: as per comment here is an execution plan from the SEDE for the simple statement below:

SELECT ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
       Id AS [User Link], 
       Reputation
  FROM Users
 WHERE id = 1 
 ORDER BY Reputation DESC;

enter image description here

This information is very helpful for query optimization - in particular for more complex and long running statements.

  • Surely a plan would just run the query? What data would the plan give? – Tim May 7 '15 at 19:40
  • 1
    Creating an execution plan - usually done by running an Explain Plan For <query> command on the database - is not the same as executing the statement. Check for instance the Oracle documentation: docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm. The query composer offers an option "Include execution plan" which works only in combination with execution. I'd prefer a feature for creating an execution plan without running the statement, because it's usually much faster and extremely helpful to database experts. I'll add a plan as example above ... – Trinimon May 7 '15 at 19:57
  • 4
    There are actually very few indexes, which is...problematic. Looking into whether or not they can be added is on the todo list. The execution plan you get with the checkbox is the actual execution plan, which is indeed only available after also running the query–I'd like to add the estimated execution plan, but due to how the data is reloaded I don't know if the statistics collected are useful enough to produce useful results. Definitely something to look into though. – Tim Stone May 7 '15 at 20:20
  • @Tim Stone: first of all many thanks for your feedback! I'm not 100% sure which RDBMS you are using (guess it's a SQL-Server) but as far as I know it from Oracle it's not a big deal to analyze tables/schemas for statistics - just something like ANALYZE table <table> compute statistics; (oradev.com/create_statistics.jsp). This can usually be done in a batch. In the end the database takes its advantage of fresh statistics as well :) – Trinimon May 8 '15 at 7:27
5

There are no foreign keys defined in the imported databases. I have an open feature request for that here.

As the rdbms SEDE runs its query on is SqlSever and we have select permission on the schema tables/views we can query for the available indexes:

select object_name(i.object_id) as [table]
     , i.name
     , i.type_desc
     , i.is_unique
from sys.indexes i

or use the INFORMATION_SCHEMA views:

select *
from information_schema.table_CONSTRAINTS 

You could get SEDE to only report the execution plan by using the SET SHOWPLAN_TEXT statement

SET SHOWPLAN_TEXT ON
go
select * from posts
go

Do notice that this gives you a multiple resultset, click on the number to cycle through them. multiple result set

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .