SQL Command in Crystal XI

[lorri]

I have to use a SQL command in order to write a crystal report and join 2 tables. The reason for this is that I need to link 2 fields but remove a few letters at the beginning or one to get them to link

FROM "mdb_rpt"."dbo"."gci_requests" "gci_requests"
LEFT OUTER JOIN "mdb_rpt"."dbo"."gci_act_log" "gci_act_log" ON ('cr:' + cast("gci_requests"."id" as nvarchar(15))) = "gci_act_log"."call_req_id"

I have a select statement at the beginning that pulls all my fields from each table. The SQL statement works fine although it is so slow when I am refreshing it. Is there anything I can do to speed this up, either modify my statement or a setting with in Crystal?

Help please......Lorri

 

[smcnulty2000]

The problem is on the SQL side not on the crystal side.

I'm guessing this is a Microsoft Sql server query based on the names. Assuming that is true you may want to consider going to one of the sql server forums if you can't get an answer here.

Actually the entire statement from the term 'select' all the way through the stuff you've posted is the select statement. Not just the part that lists the items you are returning.

Here's what is going on. In your join, when you strip out those characters to get this to join, you are forcing that entire table to be examined. In a normal join the system might be able to use an index, which would (typically) speed things up immensely.

There are lots of tricks to improve this. I usually try to turn the left outer join into a set of subqueries if possible. I'd also see if substring (in your sql commands) might not run faster than changing the size of the datatype on the fly. That would replace your CAST statement.

You should post your whole query in this case, tell us what database brand you are working on and the version number.

Additionally, if you can tell us how many records are in each table that would be marginally helpful with seeing your full query.

 

[smcnulty2000]

Okay. That's not quite accurate, I wrote that in a hurry.

What is more correct to say is that you are forcing both tables to be examined in full rather than the system using only what it needs.

I would have this conversation with the DBA who runs your system. Some DBAs are knowledgeable on this subject, and helpful. All certified DBAs are supposed to be knowledgeable on this, although some aren't.

I'm also curious if you have any where clauses in your crystal that could be optimized, or moved into the sql statement. So if you could post that information (your record selection formula) as well that would be helpful.

 

[lorri]

I want to use crystal for the rest of my grouping, parameters, etc.
I am running this over an ODBC connection. Since I need to join the tables by modfiying a field - this is the only way I came up with this statement

Thoughts

 

[smcnulty2000]

So post it.
In full.

Maybe someone here can re-write the query to your benefit.

 

[lorri]

Query in full

SELECT "gci_requests"."id","gci_requests"."category_name",
"gci_requests"."persid", "gci_requests"."ref_num",
"gci_requests"."summary", "gci_requests"."description",
"gci_requests"."open_date", "gci_requests"."last_modified_date",
"gci_requests"."close_date", "gci_requests"."resolve_date",
"gci_requests"."call_back_date", "gci_requests"."status",
"gci_requests"."active_flag", "gci_requests"."time_spent_sum",
"gci_requests"."last_mod_by", "gci_requests"."rootcause",
"gci_requests"."log_agent", "gci_requests"."assignee",
"gci_requests"."group_id", "gci_requests"."customer",
"gci_requests"."charge_back_id", "gci_requests"."affected_rc",
"gci_requests"."support_lev", "gci_requests"."category",
"gci_requests"."solution", "gci_requests"."impact",
"gci_requests"."priority", "gci_requests"."urgency",
"gci_requests"."severity", "gci_requests"."extern_ref",
"gci_requests"."last_act_id", "gci_requests"."cr_tticket",
"gci_requests"."parent", "gci_requests"."template_name",
"gci_requests"."sla_violation", "gci_requests"."predicted_sla_viol",
"gci_requests"."macro_predict_viol", "gci_requests"."created_via",
"gci_requests"."call_back_flag", "gci_requests"."change",
"gci_requests"."incident_priority", "gci_requests"."problem",
"gci_requests"."type", "gci_requests"."sched_token",
"gci_requests"."event_token", "gci_requests"."service_type",
"gci_requests"."severity_num", "gci_requests"."urgency_num",
"gci_requests"."impact_num", "gci_requests"."assignee_lastname",
"gci_requests"."assignee_firstname", "gci_requests"."assignee_middlename",
"gci_requests"."assignee_organization",
"gci_requests"."assignee_adaccount", "gci_requests"."assignee_phone",
"gci_requests"."assignee_department", "gci_requests"."assignee_title",
"gci_requests"."assignee_email", "gci_requests"."customer_lastname",
"gci_requests"."customer_firstname", "gci_requests"."customer_middlename",
"gci_requests"."customer_organization",
"gci_requests"."customer_adaccount", "gci_requests"."customer_phone",
"gci_requests"."customer_department", "gci_requests"."customer_title",
"gci_requests"."customer_email", "gci_requests"."reportedby_lastname",
"gci_requests"."reportedby_firstname",
"gci_requests"."reportedby_middlename",
"gci_requests"."reportedby_organization",
"gci_requests"."reportedby_adaccount", "gci_requests"."reportedby_phone",
"gci_requests"."reportedby_department",
"gci_requests"."reportedby_jobtitle", "gci_requests"."reportedby_email",
"gci_requests"."group_name", "gci_requests"."groupID",
"gci_requests"."status_name", "gci_requests"."priority_num",
"gci_requests"."customer_region","gci_requests"."assignee_location",
"gci_requests"."customer_location", "gci_requests"."reportedby_location",
"gci_requests"."assignee_region", "gci_requests"."reportedby_region",
"gci_requests"."assignee_service_type",
"gci_requests"."customer_service_type",
"gci_requests"."reportedby_service_type",
"gci_requests"."assignee_account_type",
"gci_requests"."customer_account_type",
"gci_requests"."reportedby_account_type","gci_act_log"."call_req_id",
"gci_act_log"."time_stamp", "gci_act_log"."type",
"gci_act_log"."activity_type", "gci_act_log"."analyst_lastname",
"gci_act_log"."analyst_ad", "gci_act_log"."analyst_middlename",
"gci_act_log"."analyst_firstname"

FROM "mdb_rpt"."dbo"."gci_requests" "gci_requests" LEFT OUTER JOIN
"mdb_rpt"."dbo"."gci_act_log" "gci_act_log" ON ('cr:' +
cast("gci_requests"."id" as nvarchar(15))) = "gci_act_log"."call_req_id"

 

[smcnulty2000]

Exactly what was needed.

Do you have multiple entries in gci_act_log for any entry in "mdb_rpt"."dbo"."gci_requests", OR will there only be one when any occur?

 

[lorri]

I am pulling all the fields that I want - I guess I can take out some that I don't need

I didn't know if there was a better way to write the statement - I want to have both tables linked so I can write multiple reports off of the same statement - like having a dictionary or one tabel - you know?

 

[smcnulty2000]

Okay, try this:

 FROM   "mdb_rpt"."dbo"."gci_requests" "gci_requests" LEFT OUTER JOIN 
"mdb_rpt"."dbo"."gci_act_log" "gci_act_log" ON ('cr:' + 
substring("gci_requests"."id",1,15) ) = "gci_act_log"."call_req_id"

Unfortunately T-SQL isn't my forte. I work with Oracle. So I'm guessing a little on syntax. You may have to correct it.

Let us know if it is slower or faster.

Yes, a re-usable view is a good thing. I wasn't actually asking if you could lose some of the fields. That might help but would mostly depend on what data type they were. If the datatype of your join fields are large you might be seeing a problem there.

Can you tell me this; when you run this, how many records do you get?? If you take out the join and run it for just the single table, how many records do you get?