Query optimisation in sybase

I need to retrieve customer MSISDN (phone no) from 22 customer databases. I have created a view for two cases:

1. First we need to check for which MSISDNs profile_id 16240 is inactive.This can be done by querying in database whose inactive data is not null.

2. Since for GPRS we have two profile 25054 and 16240,it happens e for MSISDNs 25054 (for internet purpose) is active and 16240 (for GPRS is not active) so we need to create script for that purpose .

I have prepared a query:

CREATE VIEW SUBSCR_INFO_VIEW AS
SELECT subscr_no,account_no FROM CUSTOMER_PROFILE_DEF WHERE subscr_no NOT IN
(SELECT DISTINCT(subscr_no) FROM CUSTOMER_ID_EQUIP_MAP
WHERE inactive_date Is NOT NULL)
 AND (profile_id IN (16240) AND cutoff_end_dt IS NOT NULL) OR (profile_id IN (25054) AND profile_id NOT IN (16240) AND cutoff_end_dt IS NULL)

SET ROWCOUNT 100 

SELECT DISTI开发者_运维技巧NCT(subscr_no) FROM SUBSCR_INFO_VIEW 

This will be hit in all 22 customer servers and to take data from a single customer it's taking 2.5 min. I want to reduce that time. Please let me know your feedback.

This is a little difficult to answer without knowing more about the structure of the database. How many records do you have in the CUSTOMER_PROFILE_DEF and CUSTOMER_ID_EQUIP_MAP tables, and what keys do you have? Also, your SQL is very difficult to understand in the original post, I have reformatted it below and made some small changes:

    CREATE VIEW 
        SUBSCR_INFO_VIEW 
    AS SELECT 
        subscr_no,
        account_no 
    FROM 
        CUSTOMER_PROFILE_DEF 
    WHERE 
        subscr_no 
    NOT IN (
            SELECT DISTINCT
                subscr_no
            FROM 
                CUSTOMER_ID_EQUIP_MAP 
            WHERE
                inactive_date Is NOT NULL
        ) 
    AND ((profile_id = 16240 AND cutoff_end_dt IS NOT NULL) 
    OR   (profile_id = 25054 AND cutoff_end_dt IS NULL))

    SET ROWCOUNT 100 -- This is just for testing?

    SELECT DISTINCT(subscr_no) FROM SUBSCR_INFO_VIEW

The sql is largely the same, but I changed your profile_id in (12345) statements to profile_id = 12345 as there was only one value in the list of values.